Link to home
Start Free TrialLog in
Avatar of jenit
jenit

asked on

Excel Macro - search column find value calculate other columns

Hello I'm trying to make a macro that will search thru two columns of data E and F in my file.  Then if it finds a specific value there say "BWS" then multiply column K*0.5 and paste the result in P, all in the same row.  If "BWS" is not found, the cell in K will be empty or 0

I've attached a sample sheet of data and below is what I have tired, but is totally NOT working

Range("E:F").Select

Dim Sales As String, result As String
    Dim rng As Range
   
    For Each rng In Range("E:F")
        If (rng.Value = "") Then Exit For
       Sales = rng.Value
If Sales = "BWS" Then
    result = "=RC[-11]*0.5"
 
Range("P2").Value = result

  End If
        rng.Offset(0, -1).Value = result
    Next rng
weeklyTEST.xlsx
Avatar of Saurabh Singh Teotia
Saurabh Singh Teotia
Flag of India image

You can do something like this what you are looking for...

    Dim rng As Range, lrow As Long, cell As Range

    lrow = Cells(Cells.Rows.Count, "E").End(xlUp).Row

    Set rng = Range("E2:E" & lrow)


    For Each cell In rng
        If UCase(Trim(cell.Value)) = "BWS" Or UCase(Trim(cell.Offset(0, 1).Value)) = "BWS" Then
            Cells(cell.Row, "P").Value = Cells(cell.Row, "K").Value * 0.5
        Else
            Cells(cell.Row, "K").Value = ""
        End If


    Next cell

Open in new window


Saurabh...
SOLUTION
Avatar of Rob Henson
Rob Henson
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jenit
jenit

ASKER

Rob, That is much more simple,  I do like it.

However this is a small piece of a larger macro.  Is there a way to take what you have there and copy it down to the last row of data?  We can look the the first empty in column K.
Lets assume you already have that formula in the first cell of column P and no doubt subsequent cells but not enough when the data expands, if there is data all the way down in either O or Q (neighbouring columns), double clicking the bottom right hand corner of a cell in P will fill down to the extent of the data in O or Q.

Alternatively, if you convert the list to a table, the formula will get copied down automatically as the table expands.

Thanks
Avatar of jenit

ASKER

That is cool to know as I didn't know that before.  

But I need all of this to be automatic w/in my macro.  I write them (with help) and then pass them to others to run weekly.  I need them monkey proof with no user intervention needed other than how to start the process and what to do with the file when it is complete.
Jenit,

One quick thing you said you want to delete the value in Column K where BWS is not found..you can't do that with formula..you need to do with macro to delete the value in column-k

Saurabh...
Avatar of jenit

ASKER

I must have miss stated that .  I do not want to delete K. and that last line should have been P not K - P can be an empty cell or the value of 0.
Jenit in that case you can do something like this to do what you are looking for...

Dim lrow As Long
    lrow = Cells(Cells.Rows.Count, "E").End(xlUp).Row

Range("P2:P " & lrow).Formula = "=IF(OR(E2=""BWS"",F2=""BWS""),K2*0.5,0)"

'AND IF YOU WANT to convert this to value use this..

Range("P2:P " & lrow).Value = Range("P2:P " & lrow).Value

Open in new window


Credit to Rob for the formula...this will fill the formula from row-2 to the last cell of the range...

Saurabh...
Avatar of jenit

ASKER

Thanks both of you, and Saurabh, that really looks like it will work, but I am getting an error on the range line.  If i hover over, the error is this.  I have tried w/out the 2 sets of quotes (left one)""BWS"", but I'm still getting the error.  

Range("P2"_ " & lrow).Formula = "=IF<Method 'Range' of object'_Global'failed>
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of jenit

ASKER

Thanks to both of you.  It's because of folks willing to help like you guys, that dabblers in code like me look good to end users.