Conditional Statement not firing 2

[Jimmylaki]

Hello I am trying to select text in a range and if the case is met then have a statment placed in a second range. The problem is I can see the selection of range works but for the case nothing seems to fire. Could I have some pointers on my error please. Here is my code.

Sub TEST()
Dim LASTROW As Integer


LASTROW = Range("A1").End(xlDown).Row


Sheets("FXT Deals").Range("E1:AM" & LASTROW).Select

Select Case Range("H1:H" & LASTROW).TEXT

Case Is = "JPYAUD"


Range("AF1:AF" & LASTROW).T = "Market Convention is AUDJPY-within range"

Case "JPYEUR"

Range("AC1:AC" & LASTROW).Value = "Market Convention is EURJPY-within range"

Case Else


End Select



End Sub

 

[combo]

1. Test what is the value of LASTROW - Range without preceeding shhet reference returns active sheet.
2. Range("H1:H" & LASTROW).TEXT will return Null if has more than one cell.
3. There is no 'T' property/method for Range object.

combo

 

[Jimmylaki]

Hello thanks for the tips I have amended the code to the following but a mismatch error on the case JPYAUD=empty
the lastrow is = 12 in this example (ie it is selecting the whole range of data I want checked.
Sub TEST()
Dim LASTROW As Integer


LASTROW = Range("A1").End(xlDown).Row

Range("E1:AM" & LASTROW).Select



Select Case Range("H1:H" & LASTROW).Value

Case JPYAUD

Range("AF1:AF" & LASTROW).TEXT = "Market Convention is AUDJPY-within range"

Case Else
End Select
End Sub

 

[combo]

You can set values in a range with multiple cells in one go, but you can read them only cell by cell (the 2 above). So you need either to pick one cell and test it or loop cells within the range.

combo

 

[Jimmylaki]

Thanks for the tip Combo. I tested the condition on one cell which worked fine i just need to work out now how to check and apply for the rest of the cells. thank you JL

 

[HarleyQuinn]

Something like:

For Each c In Range("H1:H" & LASTROW)
            Select Case c.Value
             
                Case "JPYAUD"
             
                    Range("AF1:AF" & LASTROW).Value = "Market Convention is AUDJPY-within range"
                    Exit For
               
                Case "JPYEUR"
         
                    Range("AC1:AC" & LASTROW).Value = "Market Convention is EURJPY-within range"
           
                Case Else
           

            End Select
            
            Next c

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[Jimmylaki]

Tnx HarleyQuinn, please excuse my ignorance ( i am still vba green) what wouldi need to dim c as? cell?

 

[HarleyQuinn]

I'd dim it as a Range.

Hope this helps

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[Jimmylaki]

Hello HarleyQuinn

Your suggestion is working the problem is that for the condition or Each c In Range("H1:H" & LASTROW)
Select Case c.Value

Case "JPYAUD"

then in range("AC2:AC" & LASTROW) i would expect to see "Market Convention is AUDJPY-within range" however the code seems to be writing this statement for the whole range even if the case is JPYEUR or JPYCAD or blank. I have tried tweaking the code but nothing seems to work. What have I overlooked?

 

[HarleyQuinn]

Could you post the exact code you're now using please?

Thanks

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post

 

[Jimmylaki]

Hi HarleyQuinn,

I now have the code working. this is what i am using

For Each c In Sheets("FXT Deals").Range("H1:H" & LASTROW).Cells

If c.Value = "JPYAUD" Then
c.Offset(, 21).Value = "Market Convention is AUDJPY-within range"

End If

If c.Value = "JPYCAD" Then
c.Offset(, 21).Value = "Market Convention is CADJPY-within range"

End If

etc etc
thanks for your guidance.

 

[HarleyQuinn]

Glad you've got it sorted

HarleyQuinn
---------------------------------
Carter, hand me my thinking grenades!

You can hang outside in the sun all day tossing a ball around, or you can sit at your computer and do something that matters. - Eric Cartman

Get the most out of Tek-Tips, read FAQ222-2244: How to get the best answers before post