Solved

VB CODE

Posted on 2011-02-23
7
162 Views
Last Modified: 2012-08-14
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
Comment
Question by:Seamus2626
  • 3
  • 2
  • 2
7 Comments
 
LVL 11

Expert Comment

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

Expert Comment

by:Runrigger
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

by:broomee9
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

Open in new window

Example.xls
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Seamus2626
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

by:Seamus2626
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

by:
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

Open in new window

0
 

Author Closing Comment

by:Seamus2626
ID: 34962675
Perfect!

Thanks
Seamus
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

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

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

Join & Ask a Question