I have a worksheet that has blocks of part/order numbers that have to
be sorted by date and segmented with a blank row between each block. I
know it's not the best practice to have blank rows in between data but
thats what management wants. So I need help with code to search through
this worksheet, ignore 1 blank row between each segment, and find the
end of the data on the sheet. At that point I will insert another blank
row then insert data from a Sales Order which I have already coded.

I appreciate any help,

Thank you,

Chad

lastRow = cells(rows.count,1).End(xlup).Row


--
Regards,
Tom Ogilvy



" wrote:

I have a worksheet that has blocks of part/order numbers that have to
be sorted by date and segmented with a blank row between each block. I
know it's not the best practice to have blank rows in between data but
thats what management wants. So I need help with code to search through
this worksheet, ignore 1 blank row between each segment, and find the
end of the data on the sheet. At that point I will insert another blank
row then insert data from a Sales Order which I have already coded.

I appreciate any help,

Thank you,

Chad

Instead of moving top down try moving bottom up something like this...

dim rng as range

set rng = Sheets("Sheet1").cells(rows.count, "A").end(xlUp).offset(2,0)
msgbox rng.address
rng.select 'if you want to select the cell

--
HTH...

Jim Thomlinson


" wrote:

I have a worksheet that has blocks of part/order numbers that have to
be sorted by date and segmented with a blank row between each block. I
know it's not the best practice to have blank rows in between data but
thats what management wants. So I need help with code to search through
this worksheet, ignore 1 blank row between each segment, and find the
end of the data on the sheet. At that point I will insert another blank
row then insert data from a Sales Order which I have already coded.

I appreciate any help,

Thank you,

Chad

On Jan 26, 1:51 pm, Jim Thomlinson
wrote:
Instead of moving top down try moving bottom up something like this...

dim rng as range

set rng = Sheets("Sheet1").cells(rows.count, "A").end(xlUp).offset(2,0)
msgbox rng.address
rng.select 'if you want to select the cell

--
HTH...

Jim Thomlinson

Thanks to both of you, coming from the bottom up definitely saved some
processing time.

One more thing I'm having trouble with (sorry no new post) is this
formula, I can't seem to set it to this cell:

Range("E6").Value =
"=IF(ISBLANK(VLOOKUP(E5,JobSheet,2,FALSE)),"",(VLO OKUP(E5,JobSheet,2,FALSE)))))"
The formula works when manually pasting it in but in my situation I
have to clear the sales order and unfortunately Excel will not hold
formulas behind cell data.

Thanks again

Hello,

I believe that:

Range("E6").FormulaR1C1 =
"=IF(ISBLANK(VLOOKUP(E5,JobSheet,2,FALSE)),"",(VLO OKUP(E5,JobSheet,2,FALSE)))))"

will work for you.

Alan

"The only dumb question is the question not asked."

wrote in message
oups.com...


On Jan 26, 1:51 pm, Jim Thomlinson
wrote:
Instead of moving top down try moving bottom up something like this...

dim rng as range

set rng = Sheets("Sheet1").cells(rows.count, "A").end(xlUp).offset(2,0)
msgbox rng.address
rng.select 'if you want to select the cell

--
HTH...

Jim Thomlinson

Thanks to both of you, coming from the bottom up definitely saved some
processing time.

One more thing I'm having trouble with (sorry no new post) is this
formula, I can't seem to set it to this cell:

Range("E6").Value =
"=IF(ISBLANK(VLOOKUP(E5,JobSheet,2,FALSE)),"",(VLO OKUP(E5,JobSheet,2,FALSE)))))"
The formula works when manually pasting it in but in my situation I
have to clear the sales order and unfortunately Excel will not hold
formulas behind cell data.

Thanks again

On Jan 26, 10:53 pm, "Alan" wrote:
Hello,

I believe that:

Range("E6").FormulaR1C1 =
"=IF(ISBLANK(VLOOKUP(E5,JobSheet,2,FALSE)),"",(VLO OKUP(E5,JobSheet,2,FALSE)))))"

will work for you.


Thanks for the help Allen, I continue to receive the "Application-
defined or object-defined error." RT 1004. It has something to do with
the IF statement. I can set the cells value to the formula without the
"=" sign and it will take, I can also use the formula without the
ISBLANK statement. It has something to do with the two "" in the
middle of the string.

Please let me know if you have any more ideas

Thanks

Chad

Hi Chad,

If you are referencing "" in a cell and inputting the formula with VBA the
the "" needs to be """", thus

Range("E6").FormulaR1C1 =
"=IF(ISBLANK(VLOOKUP(E5,JobSheet,2,FALSE)),"""",(V LOOKUP(E5,JobSheet,2,FALSE)))"

Alan


wrote in message
oups.com...


On Jan 26, 10:53 pm, "Alan" wrote:
Hello,

I believe that:

Range("E6").FormulaR1C1 =
"=IF(ISBLANK(VLOOKUP(E5,JobSheet,2,FALSE)),"",(VLO OKUP(E5,JobSheet,2,FALSE)))))"

will work for you.


Thanks for the help Allen, I continue to receive the "Application-
defined or object-defined error." RT 1004. It has something to do with
the IF statement. I can set the cells value to the formula without the
"=" sign and it will take, I can also use the formula without the
ISBLANK statement. It has something to do with the two "" in the
middle of the string.

Please let me know if you have any more ideas

Thanks

Chad

On Jan 29, 1:27 pm, "Alan" wrote:
Hi Chad,

If you are referencing "" in a cell and inputting the formula with VBA the
the "" needs to be """", thus

Range("E6").FormulaR1C1 =
"=IF(ISBLANK(VLOOKUP(E5,JobSheet,2,FALSE)),"""",(V LOOKUP(E5,JobSheet,2,FALSE)))"

Alan


Thanks again Alan, I don't want to be a pest but the cell references
are showing up 'E5' as below;

=IF(ISBLANK(VLOOKUP('E5',JobSheet,2,FALSE)),"",(VL OOKUP('E5',JobSheet,
2,FALSE)))

The formula you recommended does get set without any errors, it just
changes the cell references. I'm sure I just need to add additional
characters to allow it as a cell.

Thanks

Chad

If E6 contains the formula and you are referencing E5 as the lookup value
you can use the following formula for E6:

ActiveCell.FormulaR1C1 = _
"=IF(ISBLANK(VLOOKUP(R[-1]C,JobSheet,2,FALSE)),"""",(VLOOKUP(R[-1]C,JobSheet,2,FALSE)))"

Alan

wrote in message
ups.com...


On Jan 29, 1:27 pm, "Alan" wrote:
Hi Chad,

If you are referencing "" in a cell and inputting the formula with VBA
the
the "" needs to be """", thus

Range("E6").FormulaR1C1 =
"=IF(ISBLANK(VLOOKUP(RC[1,JobSheet,2,FALSE)),"""",(VLOOKUP(E5,JobSheet,2,F ALSE)))"

Alan


Thanks again Alan, I don't want to be a pest but the cell references
are showing up 'E5' as below;

=IF(ISBLANK(VLOOKUP('E5',JobSheet,2,FALSE)),"",(VL OOKUP('E5',JobSheet,
2,FALSE)))

The formula you recommended does get set without any errors, it just
changes the cell references. I'm sure I just need to add additional
characters to allow it as a cell.

Thanks

Chad