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
jenitAsked:
Who is Participating?
 
Saurabh Singh TeotiaCommented:
My bad you have extra space in it which you can remove..it will look like this...

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

Open in new window


Rest everything remains the same...
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
Rob HensonFinance AnalystCommented:
Or you can do it with formula in column P, copied down as far as required:

=IF(OR(E1="BWS",F1="BWS"),K1*0.5,0)

Thanks
Rob H
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
jenitAuthor Commented:
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.
0
 
Rob HensonFinance AnalystCommented:
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
0
 
jenitAuthor Commented:
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.
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
jenitAuthor Commented:
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.
0
 
Saurabh Singh TeotiaCommented:
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...
0
 
jenitAuthor Commented:
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>
0
 
jenitAuthor Commented:
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.
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.

All Courses

From novice to tech pro — start learning today.