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:

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!

EDIT: If formulas can be used for this that'll work too. I can incorporate them into my VBA code. I'm messing with the Index and Match, but I'm not having any luck. I basically need to look up two different values and return two different values (and subtract them). I'm not sure if this is possible in a formula.

Hi,

Is there a reason this needs to be done with VBA?

There are formulas you could use to complete this calculation directly in the cell.

EDIT: Your edit answered this question.

I'm trying to automate this process as much as possible. This is a bit of a pain to do manually. The workbook that I'm wanting to find all this information in cannot be written in. Can I do this in the workbook I want to copy the final result to?

You can have the formula in the second workbook and have it reference the first, certainly.

But I suppose it really depends on what you're going to do with that second workbook after the calculation. Maybe you'd still need VBA to make the looked up value static rather than the shown result of a formula.

My VBA knowledge isn't great so you may be better placed to decide if that would work for you or if a full VBA solution would be worth hanging on for.

KeriM brought next idea :
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.

Well, following your example you'll get the values:
20
50
40
20
to be written in another worksheet.
Randomly?

Bruno

No, I have specific cells I want these numbers to go into. I was going to specify that in my code. I've got that part of the code down (putting it into the other worksheet and specifying where to put it), but I just don't know about the rest.

Do you know of any formulas that will work for this? I added this into my post, but basically I need to lookup two values and return two values (as well as subtract them). I was playing with index and match, but I'm not having too much luck.

On Monday, August 13, 2012 10:48:25 AM UTC-5, 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!









--

KeriM

Send file to dguillett1 @gmail.com with this msg and a complete explanation with examples.

So you need to show three values (first, second and the difference between) for each combination in columns B & C?

I have a horrid feeling I'm completely misunderstanding what you're trying to achieve here... perhaps an example workbook would be a good idea?

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:

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!

As usual there are many ways to address the next requirements for
testing the cell contents as you describe below. But the simplest
solution I would use would be to test the cell to see if it contains
"a" or "1" by using the instr function like this:

For i = first_row to last_row
if instr(cells(i, "B")',"a")0 and instr(cells(i, "C"),"1")0 then _
worksheets(othersheet).cells(i, "A")= cells(i, "E") - cells(i, "F")
Next i

The second line in the loop will store your result in the same row on
the target sheet as the source sheet.

For only 400 lines of data the for loop will be very fast.


On Wed, 15 Aug 2012 14:20:15 +0000, KeriM
wrote:


John Keith;1604636 Wrote:
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!

John Keith

John,

Perfect! It does the calculation and writes it on the source worksheet.

Now, how can I get it write to another workbook? This one may be a bit tricker, because the workbook I want to write to doesn't always have the same name (it is always open though). This is how it is written (and working) for my other sheets:

When I tried to implement this code into yours, it was posting a value of 0, probably because it lost the values when I switched sheets. Can I store the answer in a variable and then call the variable to write the answer in the cell?

Update: tried it again, it works!

Thanks so much for your help John!

Now, how can I get it write to another workbook? This one may be a bit
tricker, because the workbook I want to write to doesn't always have the
same name (it is always open though).

Change this line (to the equal sign):

worksheets(othersheet).cells(i, "A")= cells(i, "E") - cells(i, "F")

to something like:

workbooks(ws.Name).activesheet.ActiveCell.FormulaR 1C1 =

You may need to tweak this a little but basically you need to
reference the workbook, then the worksheet and then the cell.


On Fri, 17 Aug 2012 20:15:09 +0000, KeriM
wrote:


John Keith;1604696 Wrote:
As usual there are many ways to address the next requirements for
testing the cell contents as you describe below. But the simplest
solution I would use would be to test the cell to see if it contains
"a" or "1" by using the instr function like this:

For i = first_row to last_row
if instr(cells(i, "B")',"a")0 and instr(cells(i, "C"),"1")0 then _
worksheets(othersheet).cells(i, "A")= cells(i, "E") - cells(i, "F")
Next i

The second line in the loop will store your result in the same row on
the target sheet as the source sheet.

For only 400 lines of data the for loop will be very fast.



John,

Perfect! It does the calculation and writes it on the source worksheet.


Now, how can I get it write to another workbook? This one may be a bit
tricker, because the workbook I want to write to doesn't always have the
same name (it is always open though). This is how it is written (and
working) for my other sheets:


Code:
--------------------


Dim ws As Workbook
If ws.Name Like "*Count*" Then
Windows(ws.Name).Select
Range("C3").Select
ActiveCell.FormulaR1C1 = 'write answer here'

--------------------


When I tried to implement this code into yours, it was posting a value
of 0, probably because it lost the values when I switched sheets. Can I
store the answer in a variable and then call the variable to write the
answer in the cell?

John Keith

John,

One more question. How do I get it to loop through all the sets of criteria and put it where I want it to go? For example, I want "a1" to go into cell C3, "a2" to go into cell C4, "b1" to go into cell C8, "b2" to go into cell C9, etc. I can do it with one set of criterion (like "a1"), but how do I combine it with all my criteria?

I was able to get it working; however, I ended up just putting each set of values in their own sub and making a big runall() at the top so it just runs through all the subs. Is there a more efficient way?

I've lost track of the detail of what you're trying to do at this
point. If you have something that works for you and runs fast enough
don't worry about being more efficient.

On Mon, 20 Aug 2012 20:02:52 +0000, KeriM
wrote:


KeriM;1604763 Wrote:
John,

One more question. How do I get it to loop through all the sets of
criteria and put it where I want it to go? For example, I want "a1" to
go into cell C3, "a2" to go into cell C4, "b1" to go into cell C8, "b2"
to go into cell C9, etc. I can do it with one set of criterion (like
"a1"), but how do I combine it with all my criteria?

I was able to get it working; however, I ended up just putting each set
of values in their own sub and making a big runall() at the top so it
just runs through all the subs. Is there a more efficient way?

John Keith

Ha ha. Fair enough. Thank you so much for your help on this!