Tech Support Guy banner
Status
Not open for further replies.

Excel 2003 replace macro based on value in other cell

2K views 4 replies 3 participants last post by  bomb #21 
#1 ·
Hello

I need to do the following in VBA:

If cell in column C contains the value GBP, change cell S from that row from 13 to 23
or
If a cell column C contains the value USD change cell S from that row from 13 to 33

exception cell: C1 --> contains the title of the column

How do I do this?
 
#2 ·
In the loop of your VBA (going down the C column)

For each xrow form 2 to ....
If cells(xrow,"C") = "GBP"Then
cells(xrow, "S") = 23
Elseif Cells(xrow,"C") = 'USD" Them
cells(xrow, "S") = 33
End if

Just translate your text to code

If cell x contains that then S = that else if cell x contains this then s = this etc etc

I hope this helps :)
 
#5 ·
OP, what is "contains"? C2 could be just (example) "GBP" or "Price in GBP".

The InStr function finds the starting position of a string within another, if it's there. So if the cell is "GBP" then InStr("GBPUSD", Cell) returns 1. If the cell is "USD" InStr("GBPUSD", Cell) returns 4.

Thus:

Sub test()
For Each Cell In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
Select Case InStr("GBPUSD", Cell)
Case 1
Range("S" & Cell.Row) = 23
Case 4
Range("S" & Cell.Row) = 33
End Select
Next Cell
End Sub
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top