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

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

Microsoft ExcelVB Script

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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.

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

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

Thanks

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.

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...

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...

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...

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

Saurabh...

```
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
```

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

Saurabh...

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>

Range("P2"_ " & lrow).Formula = "=IF<Method 'Range' of object'_Global'failed>

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

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.

Open in new window

Saurabh...