• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • 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
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

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