• Status: Solved
• Priority: Medium
• Security: Public
• Views: 170

# VB CODE

Hi,

Im looking for a sub that can do two things

(1) if Column J Contains "GBP", divide Value in Col I by 100
(2) IF Column J does not contain "GBP", Multiply value in Col I by Value in Col L and place result in K

Thanks
Seamus
Example.xls
0
Seamus2626
• 3
• 2
• 2
1 Solution

Commented:
=IF(J1="GBP",I1/100,I1*L1)
0

Commented:
Note Seamus that the above formula needs to be placed in cell K1 and dragged down as required
0

VBA DeveloperCommented:
If it has to be VB Code then try this:
``````Sub RunCalc()

Dim i As Long
Dim lastrow As Long

lastrow = Range("E" & Rows.Count).End(xlUp).Row

For i = 7 To lastrow
If Right(Trim(Cells(i, "J").Value), 3) = "GBP" Then
Cells(i, "K").Value = Cells(i, "I").Value / 100
Else
Cells(i, "K").Value = Cells(i, "I").Value * Cells(i, "L").Value
End If
Next i

End Sub
``````
Example.xls
0

Author Commented:
hey Runrigger thanks for that

I do need two seperate events though

its perfect for the non GBP, but if it does equal GBP, i need it to dived column J By 100 and leave K unaffected.

Thanks
Seamus
0

Author Commented:
Again Broome, perfect, except i need Col I to be divided by 100 if J = GBP and Column K to remain the same

(Sorry for the typo above runrigger)

Thanks
Seamus
0

VBA DeveloperCommented:
>>its perfect for the non GBP, but if it does equal GBP, i need it to dived column J By 100 and leave K unaffected.

If that's the case, then see the attached modified code.
``````Sub RunCalc()

Dim i As Long
Dim lastrow As Long

lastrow = Range("E" & Rows.Count).End(xlUp).Row

For i = 7 To lastrow
If Right(Trim(Cells(i, "J").Value), 3) = "GBP" Then
Cells(i, "I").Value = Cells(i, "I").Value / 100
Else
Cells(i, "K").Value = Cells(i, "I").Value * Cells(i, "L").Value
End If
Next i

End Sub
``````
0

Author Commented:
Perfect!

Thanks
Seamus
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

## Featured Post

• 3
• 2
• 2
Tackle projects and never again get stuck behind a technical roadblock.