Can not understand what it means!

[krishnak]

Hi All,

I am trying to decipher the macros written for a spreadsheet with many tabs. I am copying a line of code which I can not make out:

Range("C" & FirstCopyRow & ":F" & FirstCopyRow + DataRowsperSheet - 1).Formula =
"=SUMIF(All!$A$2:$A$3000,""*""&$C$2&""*""&$B" & FirstCopyRow & ",All!F$2:F$3000)"

There are a couple of more lines code in the same manner.
Range part is simple. What does "All! " mean? I do not see a worksheet with the name "All". Does it mean all the worksheets in the workbook?
I am not sure what the formula will do to the range itself? I searched this forum and could not find a satisfactory answer. Can anyone please explain what it means or direct me to a source where some explanation is available.
Unfortunately I can not share the workbook.

Thanks in advance

- Krishna

[mdmackillop]

Hi Hrishna,
Rename a worksheet as All. Put this code in a standard module and run it in another sheet. You'll see the resultanr formulae in a block of cells.

Sub Test()
Firstcopyrow = 10
datarowspersheet = 5

Range("C" & Firstcopyrow & ":F" & Firstcopyrow + datarowspersheet - 1).Formula = _
"=SUMIF(All!$A$2:$A$3000,""*""&$C$2&""*""&$B" & Firstcopyrow & ",All!F$2:F$3000)"
End Sub

[krishnak]

Hi md,

Thanks for the reply. Sorry for the delay in replying.

I created another workbook with two sheets - one sheet is named "All". I entered some values in the ranges A:A and F:F. I entered a simple value of 3 in the "C2" of the second worksheet (Sheet2). If I put a simple comparison operator like "">""&$C$2 I am getting the results in the range specified. But I can not understand the expression -
,""*""&$C$2&""*""&$B" & Firstcopyrow & ". I'll appreciate if you can explain me the expression with some detail.
What does the first multiplications sign (is this a multiplication sign?) do? Or is it some sort of a regular expression?
In my worksheet if I try "">""&C$2 ""*""""3"" for comparison, I must get the sum of all the corresponding values above 9. But I am getting 0 all the time. Probably I am commiting some syntax error.
Please help.

- Krishna

[mdmackillop]

Sumif is looking for a sting in the format *?*? where * is the literal character. ? could be letter or number

[Bob Phillips]

The VBA is creating a string to create a formula in a worksheet. The target formula is

"=SUMIF(All!$A$2:$A$3000,"*"&$C$2&"*&$B10,All!F$2:F$3000)"

which looks in A2:A3000 for any cell that CONTAINS the strings in C2 and B10, and if so adds up the corresponding value in B2:B3000.

So if C2 = ABC and B10 = XYZ, the following cells match

- some text ABC some more text XYZ even more text
- some text ABC some more text XYZ
- ABC some more text XYZ even more text
- some text ABCXYZ
- ABCXYZ some text

and so the B values get summed. Thes cells fail

- some text AB C some more text XYZ even more text
- some text ABC some more text X YZ even more text
- some text XYZ some more text ABC even more text

and so the B values are not summed.

[mdmackillop]

That make much more sense!

[krishnak]

Hi All,

Thank you very much for a detailed explanation. The "*" notation representing a character makes it clear. I was thinking in terms of the multiplier.
As I mentioned earlier, I am trying to unravel the macros written and handed over by an expert with no explanation whatsoever. I am still to find the sheet named "All" in the workbook and what it does.
Anyway, thanks to you again, good progress is made in understanding the code where I was getting stumped.

- Krishna

[Bob Phillips]

It might be very hidden.

Try enetering

Worksheets("All").Visible = xlSheetVisible

in the immediate window in the VBIDE

[krishnak]

Thanks, that is what I did soon after my previous post. I found that two worksheets are hidden.
- Krishna