Solved

# VB CODE

Posted on 2011-02-23
156 Views
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
Question by:Seamus2626
• 3
• 2
• 2

LVL 11

Expert Comment

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

LVL 11

Expert Comment

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

LVL 24

Expert Comment

ID: 34962514
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 Comment

ID: 34962527
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 Comment

ID: 34962546
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

LVL 24

Accepted Solution

broomee9 earned 500 total points
ID: 34962550
>>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 Closing Comment

ID: 34962675
Perfect!

Thanks
Seamus
0

## Join & Write a Comment Already a member? Login.

### Suggested Solutions

Sparklines have been introduced with Excel 2010 and are a useful tool for creating small in-cell charts, used for example in dashboards. Excel 2010 offers three different types of Sparklines: Line, Column and Win/Loss. What it does not offer is a…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

#### 708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

#### Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!