Joining String variables... struggling

[maykinit]

Dim Var1 As String, Var2 As String
Var1 = InputBox("Variable 1?")
Var2 = InputBox("Variable 2?")

If I have two string variables defined as:

sFirst = "ActiveCell.Value = Val(Var1)"
sSecond = "ActiveCell.Offset(, 1).Value = Val(Var2)"

The value of ActiveCell and ActiveCell.Offset matches that of Var1 and Var2

I wonder if someone could advise what I am doing wrong when the following will not work? What is it I am doing wrong? I get "run time error '13': Type Mismatch". Thanks so much.

If (sFirst) & " " & "And" & " " & (sSecond) Then
MsgBox "works"
End If

 

[shg]

That's not how the language works.

    Dim s1          As String
    Dim s2          As String
    Dim b1          As Boolean
    Dim b2          As Boolean
 
    s1 = InputBox("Variable 1?")
    s2 = InputBox("Variable 2?")
 
    b1 = ActiveCell.Value = Val(Var1)
    b2 = ActiveCell.Offset(, 1).Value = Val(Var2)
 
    If b1 And b2 Then
        MsgBox "works"
    End If

 

[maykinit]

That's not how the language works.

    Dim s1          As String
    Dim s2          As String
    Dim b1          As Boolean
    Dim b2          As Boolean
 
    s1 = InputBox("Variable 1?")
    s2 = InputBox("Variable 2?")
 
    b1 = ActiveCell.Value = Val(Var1)
    b2 = ActiveCell.Offset(, 1).Value = Val(Var2)
 
    If b1 And b2 Then
        MsgBox "works"
    End If

Thanks very much but that doesn't accomplish what I am after.

In the example I am attempting to run the following code by employing the use of variables:

If ActiveCell.Value = Val(Var1) And ActiveCell.Offset(, 1).Value = Val(Var2) then
MsgBox "works"
EndIf

 

[xenou]

Sub Foo()
Dim sFirst
Dim Var1

    Var1 = 3.14159
    sFirst = "ActiveCell.Value = Val(Var1)"
    MsgBox sFirst
    sFirst = (ActiveCell.Value = Val(Var1))
    MsgBox sFirst

End Sub

If I haven't screwed up the code here (it is untested) then the above may be an instructive example for you. Or, work through shg4421's example and understand why his works and yours doesn't. Hint: what's in quotes? What isn't in quotes?

Debugging skills are important:
http://krgreenlee.blogspot.com/2006/04/programming-excel-vba-debugging-for.html

 

[maykinit]

I understand what you are explaining to me(at least I'm 90 percent sure). That said, I'm confused as to how the examples you've offered get me to the end result I'm attempting to achieve. Perhaps I haven't explained it well? Thanks for the time...

 

[xenou]

The short answer is to take a close look at this construction:

sFirst = "ActiveCell.Value = Val(Var1)"

Everything is in quotes. So its a string literal - just characters or letters of the alphabet. What you really want is an expression, or something that evaluates to true or false.

So here's an expression:
ActiveCell.Value = Val(Var1)

It tells you if the active cell's value is equal to the value of Var1. True or False. Getting warmer...

 

[shg]

Sorry, I renamed your variables incompletely:

    Dim s1          As String
    Dim s2          As String

    Dim b1          As Boolean
    Dim b2          As Boolean
 
    s1 = InputBox("Variable 1?")
    s2 = InputBox("Variable 2?")
 
    b1 = ActiveCell.Value = Val(s1)
    b2 = ActiveCell.Offset(, 1).Value = Val(s2)
 
    If b1 And b2 Then
        MsgBox "works"
    End If

 

[maykinit]

@shg4421
@xenou

Trust me, I mean no disrespect. Sometimes in this type of forum, it is easy to be misunderstood so I just need to get that out. I have a severe case of envy for the knowledge base here. So snapping back to my dilemma. I am under the gun as it were to come up with an answer to this thing and damned if I can figure out how what you are attempting to convey to me will give me the solution I am after. So let me offer this hoping that somewhere there is a disconnect....

I want to build an expression a piece at a time so that at the tail end of the logic I can run that expression to get the result I am after. That expression would look at a row at a time on the spreadsheet (through 6 columns) for the columns that are not empty. It would then compare those cells to values contained in variables for an exact match and then return to me the value of the 7th column. I sure hope this helps. If you could/would provide any examples for me of a methodology for getting there I sure would appreciate it.

If there is a disconnect you have my apologies. My envy remains.

 

[shg]

Explanations are always good, but more would be better.

That expression would look at a row at a time on the spreadsheet (through 6 columns) for the columns that are not empty. It would then compare those cells to values contained in variables for an exact match and then return to me the value of the 7th column.

Compare non-empty values in columns A:F to six variables that are input manually, and if they all match, return the value in col G to a MsgBox? Or ...

 

[maykinit]

Yessir,

<table width="500" border="0" cellpadding="0" cellspacing="0"><col style="width: 48pt;" span="6" width="64"> <col style="width: 87pt;" width="116"> <tbody><tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt; width: 48pt;" width="64" height="17">10</td> <td class="xl77" style="border-left: medium none; width: 48pt;" width="64">102</td> <td class="xl74" style="width: 48pt;" width="64">
</td> <td class="xl74" style="width: 48pt;" width="64">
</td> <td class="xl76" style="width: 48pt;" width="64"> </td> <td class="xl76" style="border-left: medium none; width: 48pt;" width="64"> </td> <td class="xl75" style="border-left: medium none; width: 87pt;" width="116">AB861A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt; border-top: medium none;" height="17">10</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl77" style="border-left: medium none;"> </td> <td class="xl77" style="border-left: medium none;"> </td> <td class="xl76" style="border-top: medium none; border-left: medium none;">04</td> <td class="xl76" style="border-top: medium none; border-left: medium none;">600RJ</td> <td class="xl75" style="border-top: medium none; border-left: medium none;">AA861A</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt; border-top: medium none;" height="17">10</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl75" style="border-top: medium none; border-left: medium none;">280A104-05</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt; border-top: medium none;" height="17">10</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">S</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">S</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl75" style="border-top: medium none; border-left: medium none;">112A311-01</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt; border-top: medium none;" height="17">10</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl75" style="border-top: medium none; border-left: medium none;">236A183-01</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt; border-top: medium none;" height="17">10</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">102</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl76" style="border-top: medium none; border-left: medium none;">02</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl75" style="border-top: medium none; border-left: medium none;">ABF227E</td> </tr> <tr style="height: 12.75pt;" height="17"> <td class="xl77" style="height: 12.75pt; border-top: medium none;" height="17">10</td> <td class="xl77" style="border-top: medium none; border-left: medium none;">103</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl76" style="border-top: medium none; border-left: medium none;">04</td> <td class="xl77" style="border-top: medium none; border-left: medium none;"> </td> <td class="xl75" style="border-top: medium none; border-left: medium none;">AFD227B</td> </tr> </tbody></table>

My intention is to parse a "part number" into variables.
An example of a part number would be "10102SS02600RJ"
s1 = 10
s2 = 102
s3 = S
s4 = S
s5 = 02
s6 = 600RJ
An example of a part number would be "10102SS02600RJ"
I would loop through the spreadsheet a row at a time containing cells populated as illustrated above. The logic would look at a rows one at a time for only those cells that are populated. It would then do a compare of the value to that which is contained in the matching variable. If a match, the value in column 7 would be returned. So, in this example row one would return "AB861A" because the 1st column and 2nd column values are populated and match the values in both s1 and s2. Next row... row two would not return anything because even though the 1st, 2nd, 3rd, 4th and 6th columns match s1,s2,s3,s4 and s6 respectively, the value in s5 does not match. I sure hope this helps. Please let me know... and thank you.