Conditional Statements not firing

[Jimmylaki]

Hello

I have the following code which I want to perform the cases I hav
written (also I want to add another 5 or 6 similar cases to the list)
It seems that the cope runs and selects the range but does not fire th
condtion ie Market Convention is AUDJPY-within range
Could I please have some direction on what the problem seems to be (an
text is correct on the first case if I change to value it gives
runtime error)

Sub TEST()
Dim rng1 As Range
Dim LASTROW As Integer


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

Set rng1 = Range("H1:h" & LASTROW)


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


Select Case rng1.TEXT

Case "JPYAUD"


Range("AC1:AC" & LASTROW).Value = "Convention i
AUDJPY-within range"

Case "JPYEUR"

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

Case Else

Range("h1:h100").Value = " "

End Select



End Su

 

[Joel]

Try these changes. Not sure why for the case else you are putting in a
single blnak character in column H. Maybe you sould either use
clearcontents or put nothing which is two double quotes with nothing inbetween

from
.Range("h" & Cell.Row).Value = " "
to
.Range("h" & Cell.Row).Value = ""



Sub TEST()
Dim rng1 As Range
Dim LASTROW As Integer


With Sheets("FXT Deals")
LASTROW = .Range("A1").End(xlDown).Row

Set rng1 = .Range("H1:h" & LASTROW)


.Range ("A1:AM" & LASTROW)

For Each Cell In rng1

Select Case Cell.Text

Case "JPYAUD"
.Range("AC" & Cell.Row).Value = _
"Convention is AUDJPY-within range"

Case "JPYEUR"
.Range("AC" & Cell.Row).Value = _
"Convention is EURJPY-within range"

Case Else
.Range("h" & Cell.Row).Value = " "
End Select
Next Cell
End With
End Sub

 

[Patrick Molloy]

if the currency is a formula, then I'd suggest changing
Select Case Cell.Text

to
Select Case Cell.Value

also.

Patrick

 

[Jimmylaki]

Hello,

Thank you for reply. I made the changes as you recommended but stil
nothing was happening where I would have expected range AC to have th
conditional message this is my source code 'Select Case Statement i
Excel VBA Macro Code. Alternative to Multiple If, Or, And Els
Statements' (http://www.ozgrid.com/VBA/select-case.htm)

 

[Jimmylaki]

Hello,

I have made some further changes to the code which seems to work bu
for each case in range H of JPYAUD I was expecting to see the commen
Market Convention is AUDJPY-within range" for each cell representin
JPYAUD however the whole column in AC range has the comment even i
there are other conditions in the range H. Any suggestions:

Sub test()
Dim c As Range

Dim LASTROW As Integer

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

Range("A1:AM" & LASTROW).Select
For Each c In Range("H1:H" & LASTROW)
Select Case c.Value

Case "JPYAUD"

Range("AC2:AC" & LASTROW).Value = "Marke
Convention is AUDJPY-within range"
Exit For

Case Else


End Select

Next c

End Sub

 

[Simon Lloyd]

Try

Code
-------------------
Sub test(
Dim c As Rang

Range("A1:AM" & Range("AM" & rows.count).end(xlup).row).Selec
For Each c In Range("H1:H" & range("H" & & rows.count).end(xlup).row
Select Case c.Valu

Case "JPYAUD

Range("AC2:AC" & range("AC" & rows.count).end(xlup).row).Value = "Market Convention is AUDJPY-within range

Case Els

End Selec
Next
End Su
-------------------

Hello

I have made some further changes to the code which seems to work bu
for each case in range H of JPYAUD I was expecting to see the commen
Market Convention is AUDJPY-within range" for each cell representin
JPYAUD however the whole column in AC range has the comment even i
there are other conditions in the range H. Any suggestions
Code
-------------------
Sub test(
Dim c As Rang

Dim LASTROW As Intege

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

Range("A1:AM" & LASTROW).Selec
For Each c In Range("H1:H" & LASTROW
Select Case c.Valu

Case "JPYAUD

Range("AC2:AC" & LASTROW).Value = "Market Convention is AUDJPY-within range
Exit Fo

Case Els


End Selec

Next

End Su
-------------------

--
Simon Lloy

Regards
Simon Lloy
'Microsoft Office Help' (http://www.thecodecage.com

 

[Jimmylaki]

Hello Simon,

thank you for the tip. I have just tested this code and for the commen
ie range 2 AC1 and AC2 have the desired comment. For range AC1 this is
heading row which is why I made the initial range as AC2, however, th
code works for 2 cells. I will keep tweaking your code. thank you
JL