Quote:
Originally Posted by John Keith
On Mon, 13 Aug 2012 15:48:25 +0000, KeriM
wrote:
I'm trying to do a somewhat complicated find in VBA.
I have a worksheet with column B having only two different values (eg. a
and b). Column C has values that repeat for each value for column B.
(eg. 1 and 2). The values that I want to return are in columns E and F.
So what I'm trying to do is this:
If columns B and C equal "a" and "1" (respectively), then subtract the
values in E and F (on that row where the columns equal) and return that
result on another worksheet.
Repeat for all the different combinations...a1, a2, b1, b2.
To illustrate:
Code:
--------------------
Column B | Column C Column E | Column F
a 1 70 50
a 2 80 30
b 1 60 20
b 2 40 20
--------------------
I want to find a1 and write "20" in another worksheet.
Sorry if this is an overly long explanation, I feel like it's confusing
to explain. Let me know if anything needs to be clarified. As always,
any help is appreciated!
How long is your list? Is the data contiguous? Do you want to store
the result in the same row on the other worksheet?
Setting those questions aside I'd suggest either a FOR/WHILE loop. I
suppose there are more elegant methods and most experts try to avoid
loops but they can be quite fast for a simple operation like this and
the code is easy to understand.
For i = first_row to last_row
if cells(i, "B") = "a" and cells(i, "C") = 1 then _
worksheets(othersheet).cells(i, "A")= cells(i, "E") - cells(i, "F")
Next i
This all assumes I understood your question and there are several
details to check to meet what you really have to work with.
John Keith
|
John,
You understand what I'm trying to do. To answer your questions:
How long is your list?
401 rows, but that may change (I'd rather not hard-code it)
Is the data contiguous?
Yes
Do you want to store the result in the same row on the other worksheet?
I need to store the results in the same column, but different rows.
Your solution so far makes perfect sense.
I tried modifying this part for my code:
Code:
For i = first_row to last_row
If cells(i, "B") = "a" and cells(i, "C") = 1 Then....
For now, can we just select the row where it finds those two values? I want to see that it works, and I'm not sure how to do that.
I'm also having a problem with what it's supposed to equal to since those values are not the only values in the cells. It doesn't just say "a" in the column I'm searching for. It says something like "a 2342 332" and it doesn't just say "1" in column C, it says "abcdefg (1bb)." Point is, I have more than just the string I'm searching for in the cells. Do you know how I can use wildcards in the search? Thanks, you've been really helpful so far!