VBA: Pick a value if 2 conditions meet

[Legacy 143009]

I had posted to another thread before but there were no answer: http://www.mrexcel.com/forum/excel-q...nal-value.html I think it is a bit complex or I couldn't tell. Here is a related issue but something more clean another so I decided that's another case:

Check If A1=01 Then Look and Find If A2=13 or A2=12 or A2=11 or A2=10 or A2=09 or A2=08
If True Pick A2 to B1, Else
[I]Next Line 

[/I]

how can I write this?

PS: Extra work with this script is the values have prefixes as text values seperated with space. Thus they are something about playing cards, the values look like "CLUB 05" or "SPADE 12"... So the script should read the numbers after space.

 

[JLGWhiz]

maybe like this:

Code:
If Range("A1") = "01" Then
If Range("A2") > "08" And Range("A2") < "13" Then
Range("B1") = Range("A2").Value
End If
End If
Code:

 

[Legacy 143009]

You are great JLGWhiz! I am not with my project now but I'll try as soon as possible. It looks like it'll work! Do you have anything in your hat for the Post Special?

 

[hornbyOO]

Above doesn't work as the number has to be split out from the input data to do the compares, I've done a sub and function to strip out the string number and do comparison on that. It probably could be neater

Sub can go into worksheet

Sub GetData()

    Dim Cell1 As String
    Dim Cell2 As String
    Dim iA1 As Integer
    Dim iA2 As Integer
    Dim A1Flag As Boolean
    
    A1Flag = False
    Cell1 = Range("A1")
    Cell2 = Range("A2")
    iA1 = SplitData(Cell1)

    ' If A1 is a 1 go on and check A2
    If iA1 = 1 Then
        iA2 = SplitData(Cell2)
        A1Flag = True
    'This else statement is just to collect A2 value even though it doesn't
    'meet your criteria
    Else
        iA2 = SplitData(Cell2)
    End If

    If iA2 >= 8 And iA2 <= 13 And A1Flag = True Then
        MsgBox ("Parameters Met - A1 = " & iA1 & ", A2 = " & iA2)
        ' Copy Whatever Cells You Need
    Else
        MsgBox ("Parameters Not Met - A1 = " & iA1 & ", A2 = " & iA2)
    
    End If

End Sub

Function has to go into a module

Function SplitData(strIn As String) As Integer

    iLen = Len(strIn)
    iSpace = InStr(strIn, " ")
    SplitData = Right(strIn, iLen - iSpace)

End Function

 

[Legacy 143009]

hornbyOO, It looks like you have a more functional solution. But I didn't get how to combine these two scripts??

 

[JLGWhiz]

Forgot about the preceding string. This would compare only the last two digits of the ranges, but will post the entire value of A2 to B1.

Code:
If Right(Range("A1").Value, 2) = "01" Then
If Right(Range("A2").Value, 2) > "08" And Right(Range("A2").Value, 2) < "13" Then
Range("B1") = Range("A2").Value
End If
End If
Code:

 

[Legacy 143009]

Forgot about the preceding string. This would compare only the last two digits of the ranges, but will post the entire value of A2 to B1.

Code:
If Right(Range("A1").Value, 2) = "01" Then
If Right(Range("A2").Value, 2) > "08" And Right(Range("A2").Value, 2) < "13" Then
Range("B1") = Range("A2").Value
End If
End If
Code:

Yes JLGWhiz, works perfect and looks like I am gonna use this one! But it copies the value. I wanna move the value and shift cells up?!

 

[JLGWhiz]

Yes JLGWhiz, works perfect and looks like I am gonna use this one! But it copies the value. I wanna move the value and shift cells up?!

This will fill the gap.

Code:
If Right(Range("A1").Value, 2) = "01" Then
If Right(Range("A2").Value, 2) > "08" And Right(Range("A2").Value, 2) < "13" Then
Range("A2").Cut Range("B1")
Range("A2").Delete Shift:=xlUp
End If
End If
Code: