[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 167
  • Last Modified:

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
Asked:
Seamus2626
  • 3
  • 2
  • 2
1 Solution
 
RunriggerCommented:
=IF(J1="GBP",I1/100,I1*L1)
0
 
RunriggerCommented:
Note Seamus that the above formula needs to be placed in cell K1 and dragged down as required
0
 
TracyVBA 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

Open in new window

Example.xls
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Seamus2626Author 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
 
Seamus2626Author 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
 
TracyVBA 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

Open in new window

0
 
Seamus2626Author Commented:
Perfect!

Thanks
Seamus
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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