Hi,

I need an help to write VBA code for the below criteria

if column P is internal then corresponding column K value shold be zero

eg:

Column P    Column K

Revenue       $80

Revenue       $100

Internal        $90

Internal        $10

Revenue      $30

Internal        $110

The output shld be

Column P      Column K

Revenue       $80

Revenue       $100

Internal        $0

Internal        $0

Revenue      $30

Internal        $0

PLease help me

 

 

• Edited by sunabi Tuesday, September 23, 2014 3:06 AM

For example:

Sub MyMacro()
    Dim r As Long
    Dim m As Long
    Application.ScreenUpdating = False
    m = Range("P" & Rows.Count).End(xlUp).Row
    For r = 2 To m
        If Range("P" & m).Value = "Internal" Then
            Range("K").Value = 0
        End If
    Next r
    Application.ScreenUpdating = True
End Sub

not working :(

Please provide some details - in what way is it "not working"?

Hi,

Please try the sample code:

Sub test()
Dim ws As Worksheet
Dim r As Integer
Dim cel As Range
Set ws = ActiveWorkbook.Sheets("Sheet1")
r = ws.Range("P" & ws.Rows.Count).End(xlUp).Row
For Each cel In ws.Range("P1:P" & r)
    If Trim(cel.Value) = "Internal" Then
        cel.Offset(0, -5).Value = "0"
    End If
    
Next

End Sub

If you have further question about VBA, please post it to the MSDN forum for Excel

http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

Regards,

George Zhao
TechNet Community Support