Function not calculating the last row of loop 3

[benisma]

I have 4 queries built into a spreadsheet. There is a lot of manipulations that happen after that. I have figured out how to loop and use the selection.offset to be able to handle the different sizes of data. The first 4 loops work just fine. The fifth loop, that I had to write a function for skips the last line, which creates stair-steps of blank cells throughout the rest of the calculations, making my data unreliable. Please help! The fourth loop, fith loop and the function I created are below.

'4 concatenate 3ltr plcd and sub for vlookup and sums
range("J1").Select
ActiveCell.FormulaR1C1 = "Con"
range("J3").Select
Do Until Selection.Offset(0, -1).Value = ""
Selection.Value = Selection.Offset(0, -3).Value & Selection.Offset(0, -2).Value & Selection.Offset(0, -1).Value
Selection.Offset(1, 0).Select
Loop


'5 =sumif 3ltr, plcd, and sub matches, sum cost
Do Until Selection.Offset(0, -1).Value = ""
Selection.Value = ColumnS
Selection.Offset(1, 0).Select
Loop



Function ColumnS()

Dim X As Integer 'what row are you staring in?
Dim LR As Long 'last row

LR = ActiveSheet.range("G65536").End(xlUp).Row
For X = 3 To LR
Cells(X, 19).Value = "=sumif(J:J,J" & X & ",Q:Q)"
Next X
End Function


Any help would be greatly appreciated!

 

[SkipVought]

Hi,

A function returns a single value.

What single value are you returning from YOU function, ColumnS()????

Selection.Value = ColumnS

Function ColumnS()
  ..........
    ColumnS = WHAT????
  .....
End Function

Skip,

_{[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL!}

 

[benisma]

I wanted to do a sumif for a concatenated 3 letter code, product code and sub code(groups of parts). After finding the match, I wanted to sum cost on those items. I want it to look like this:

Con cost cost sum

WAL22 75.41 10858.78
WAL22 52.52 10858.78
WAL22 23.33 10858.78
WAL22 65.78 10858.78
WAL22 29.33 10858.78
WAL22 43.59 10858.78

Which it does, until the last row of the loop and then it leaves the last line blank. Then on the next loop, the loop finds the empty last line and leaves the second to last line blank. It ends up looking like this:

WAL22 10858.78 22353.82
WAL22 10858.78 22353.82
WAL22 10858.78 22353.82
WAL22 10858.78
WAL22 22353.82

Here is what I put in the function for columnS:

Function ColumnS()
Dim X As Integer
Dim LR As Long
LR = ActiveSheet.range("G65536").End(xlUp).Row
For X = 3 To LR
Cells(X, 19).Value = "=sumif(J:J,J" & X & ",Q:Q)"
Next X
End Function

I am sorry if I was not clear before, I have a hard time trying to explain what is going on, I am still learning the ropes.

 

[vbap]

Not fulling understanding what you're trying to do but the middle parameter of your SUMIF may have a space before the actual number. I think it would end up looking something like this 'SUMIF(J:J,J 3,Q:Q)' in row 3.

Try this:

For X = 3 to LR
Cells(X,19).formula="=sumif(J:J,J" & trim(str(X))& ",Q:Q)"
Next X

Also... the .End(xlUp) would find the first nonempty cell. There are other ways to find the last cell in a range.

Also lookup the 'filldown' method of a range. You can place the SUMIF function in one cell and do a 'filldown' in the range rather then doing a For..Next.

I might also use IsEmpty(Selection.offset(0,-1)) rather checking whether it's equal to "". Hey, but if it works who am I to argue.

 

[rmikesmith]

benisma,

To follow up on what Skip was driving at: While you have declared ColumnS as a function, you have not coded it as such; in fact, you are using it as a Sub procedure. Functions return a value and do so through the function's name. In this case

Function ColumnS()
  ...
  ...
    ColumnS = [i]expression[/i]

End Function

It would be less confusing if you rewrote ColumnS as a procedure.

Regards,
Mike

 

[benisma]

Thank you all.
I took the code from "vbap" and applied it as Mike and Skip suggested, worked like a charm. I really appreciate the help!