Solved

VB CODE

Posted on 2011-02-23
7
158 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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now