Solved

VB CODE

Posted on 2011-02-23
7
156 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

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.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now