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
Last Comment
jenit
SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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.
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
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.
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...
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...
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...
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>
Range("P2"_ " & lrow).Formula = "=IF<Method 'Range' of object'_Global'failed>
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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.
Open in new window
Saurabh...