Excel VBA: Creating an auto file namer

This one should be fun. I've been tinkering with this but can't figure it out, my skills are just too basic even though I know this can be accomplished with if/then statements but I'm interested in seeing who can do this more efficiently.

What I need is a VBA code that will search for the contents of a few cells and report back with a file name based on a few parameters per cell.

Example:
2 cells will be referenced, each can have 1 of two types of information (just for the sake of this exercise); if cell A2 says "Yes" then I need the VBA code to use "6000", if it says "No" then I need the VBA code to use "5000".

Then, if cell A3 says "Up" then I need the VBA code to use "Aloha", if "Down" then use "Mahalo".

I need the code to concatenate the two results with a hyphen in between the two results: like this --> 6000-Aloha or 6000-Mahalo or 5000-Aloha or 5000-Mahalo.

Another quick question, would it be possible for the VBA to not place the results in a sheet but rather perform the concatenation and place the answer in cell A10?

An example to get you started:

Code:

---

Sub aloha()
    Dim ws As Worksheet
    Dim lr As Long
    Dim j As Long
    Dim str As String
   
    Set ws = ActiveSheet
    lr = ws.Range("a" & Rows.Count).End(xlUp).Row
   
    For j = 1 To lr
        With ws
            If .Range("a" & j).Value = "yes" Then
                str = "6000"
            ElseIf .Range("a" & j).Value = "no" Then
                str = "5000"
            Else
                str = "9999"
            End If
            If .Range("b" & j).Value = "up" Then
                str = str & "-" & "aloha"
            ElseIf .Range("b" & j).Value = "down" Then
                str = str & "-" & "mahalo"
            Else
                str = str & "-" & "other"
            End If
            .Range("c" & j).Value = str
        End With
    Next j
End Sub

---

vbfbryce,

I'll start by saying thank you for your help, I'll test that code right now, also the code I'm attaching below doesn't work, this is what I tried to use.

Could you explain why it doesn't work or better yet what my fundamental problems are with how I tried to write it?

Thanks.

Code:

---

Sub NAMER()
Dim lstuno As Double
Dim lstdos As String
Dim lsttres As String
Dim result As String

lstuno = Range("a2").Value
lstdos = Range("a3").Value
lsttres = Range("a4").Value

If lstuno = "Yes" Then
    result = Range("b2").Value = "6000"
    Else: result = "5000"
    End If
   
If lstdos = "Yes" Then
    result = Range("b3").Value = "Aloha"
    Else: result = "Mahalo"
    End If
       
If lsttres = "Yes" Then
    result = Range("b4").Value = "Aloha"
    Else: result = "Mahalo"
    End If

End Sub

---

you might want to review data types and comparisons.. i'm pretty sure you're not supposed to compare a double the way that you're asking to do... i think what you want is something like, if yes is clicked then compaer lst*

Quote:

---

Originally Posted by whatever

you might want to review data types and comparisons.. i'm pretty sure you're not supposed to compare a double the way that you're asking to do... i think what you want is something like, if yes is clicked then compaer lst*

---

The information that's in cell A2 will be either rational or irrational numbers, the "Double" is used to identify the use of numbers instead of text (as "String" is for text)... I could still be using it incorrectly but that was the rationale behind using "Double". I do realize I was searching for text instead of numbers in the code. I changed it and still doesn't work.

Is there another method you know to use?

Based on your original post, here is a UserDefined function that you can use. Just use this function like any other worksheet function. This way nothing is hardcoded to specific cells. In your post #3, you are showing the use of a third parameter. I do not understand that. If it is needed to evaluate the function and you can not figure out how to implement it, just ask, but give details.

Code:

---

Public Function IGPOD(yes_no As Range, up_down As Range) As String
  Dim yn As Range
  Set yn = yes_no.Areas(1).Cells(1, 1)
  Dim ud As Range
  Set ud = up_down.Areas(1).Cells(1, 1)
  Dim badData As Boolean
  Dim ret As String
  If VarType(yn) <> vbString Or VarType(ud) <> vbString Then
      badData = True
  Else
      Select Case LCase$(yn.Value)
        Case "yes": ret = "6000-"
        Case "no": ret = "5000-"
        Case Else: badData = True
      End Select
      Select Case LCase$(ud.Value)
        Case "up": ret = ret & "Aloha"
        Case "no": ret = ret & "Mahalo"
        Case Else: badData = True
      End Select
  End If
  If badData Then
      IGPOD = CVErr(Excel.xlValue)
  Else
      IGPOD = ret
  End If
End Function

---