Hello

I have the following code which I want to perform the cases I have
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 the
condtion ie Market Convention is AUDJPY-within range
Could I please have some direction on what the problem seems to be (and
text is correct on the first case if I change to value it gives a
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 is
AUDJPY-within range"

Case "JPYEUR"

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

Case Else

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

End Select



End Sub


--
Jimmylaki
------------------------------------------------------------------------
Jimmylaki's Profile: http://www.thecodecage.com/forumz/member.php?userid=768
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133704

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



"Jimmylaki" wrote:


Hello

I have the following code which I want to perform the cases I have
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 the
condtion ie Market Convention is AUDJPY-within range
Could I please have some direction on what the problem seems to be (and
text is correct on the first case if I change to value it gives a
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 is
AUDJPY-within range"

Case "JPYEUR"

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

Case Else

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

End Select



End Sub


--
Jimmylaki
------------------------------------------------------------------------
Jimmylaki's Profile: http://www.thecodecage.com/forumz/member.php?userid=768
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133704

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

to
Select Case Cell.Value

also.

Patrick

"Joel" wrote:

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



"Jimmylaki" wrote:


Hello

I have the following code which I want to perform the cases I have
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 the
condtion ie Market Convention is AUDJPY-within range
Could I please have some direction on what the problem seems to be (and
text is correct on the first case if I change to value it gives a
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 is
AUDJPY-within range"

Case "JPYEUR"

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

Case Else

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

End Select



End Sub


--
Jimmylaki
------------------------------------------------------------------------
Jimmylaki's Profile: http://www.thecodecage.com/forumz/member.php?userid=768
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133704

Hello,

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


Joel;484809 Wrote:
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



"Jimmylaki" wrote:


Hello

I have the following code which I want to perform the cases I have
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
the
condtion ie Market Convention is AUDJPY-within range
Could I please have some direction on what the problem seems to be
(and
text is correct on the first case if I change to value it gives a
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 is
AUDJPY-within range"

Case "JPYEUR"

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

Case Else

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

End Select



End Sub


--
Jimmylaki

------------------------------------------------------------------------
Jimmylaki's Profile: 'The Code Cage Forums - View Profile: Jimmylaki'
(http://www.thecodecage.com/forumz/member.php?userid=768)
View this thread: 'Conditional Statements not firing - The Code Cage
Forums' (http://www.thecodecage.com/forumz/sh...d.php?t=133704)




--
Jimmylaki
------------------------------------------------------------------------
Jimmylaki's Profile: http://www.thecodecage.com/forumz/member.php?userid=768
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133704

Hello,

I have made some further changes to the code which seems to work but
for each case in range H of JPYAUD I was expecting to see the comment
Market Convention is AUDJPY-within range" for each cell representing
JPYAUD however the whole column in AC range has the comment even if
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 = "Market
Convention is AUDJPY-within range"
Exit For

Case Else


End Select

Next c

End Sub

Jimmylaki;487253 Wrote:
Hello,

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


--
Jimmylaki
------------------------------------------------------------------------
Jimmylaki's Profile: http://www.thecodecage.com/forumz/member.php?userid=768
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133704

Try:

Code:
--------------------
Sub test()
Dim c As Range

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

Case "JPYAUD"

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

Case Else

End Select
Next c
End Sub
--------------------
Jimmylaki;488691 Wrote:
Hello,

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


Code:
--------------------

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 = "Market Convention is AUDJPY-within range"
Exit For

Case Else


End Select

Next c

End Sub

--------------------



--
Simon Lloyd

Regards,
Simon Lloyd
'Microsoft Office Help' (http://www.thecodecage.com)
------------------------------------------------------------------------
Simon Lloyd's Profile: http://www.thecodecage.com/forumz/member.php?userid=1
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133704

Hello Simon,

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

Simon Lloyd;488708 Wrote:
Try:

Code:
--------------------
Sub test()
Dim c As Range

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

Case "JPYAUD"

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

Case Else

End Select
Next c
End Sub
--------------------



--
Jimmylaki
------------------------------------------------------------------------
Jimmylaki's Profile: http://www.thecodecage.com/forumz/member.php?userid=768
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=133704