Find last Used Cell and more

[chrismarek9]

I have a sheet where the last row can be J79 or it can be J81 depending on options selected. I have a formula in J79, J80, and J81. I need a way to copy the last cell in this column, but it is not the last cell in the whole column of the sheet. Is there a way to define a range I'm looking for the last used cell??

I'm trying to make a quote page where options can make the final price come out on J79 or J81, then the final value cell needs to be copied and pasted to a different cell with in the sheet.

I cant get the first part to work correctly.

Sub FinalAdder()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet


'Selecting the quote sheet
ActiveWorkbook.Sheets("Location Quote").Select

'supposed to find the last used cell after A78, before the next blank cell
Sheets("Location Quote").Range("A78").End(xlDown).Copy

'Pasting previous cell value to the next blank cell above J93
Sheets("Location Quote").Range("J93").End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

 

[Joe4]

Welcome to the Board!

I have a sheet where the last row can be J79 or it can be J81 depending on options selected.

Why are you using A78 instead of J78?

[COLOR=#333333]'supposed to find the last used cell after A78, before the next blank cell[/COLOR]
[COLOR=#333333]Sheets("Location Quote").Range("[/COLOR][COLOR=#ff0000]A[/COLOR][COLOR=#333333]78").End(xlDown).Copy[/COLOR]

 

[chrismarek9]

**Sorry I meant A and not J** The following post is the correct post:

I have a sheet where the last row can be A79 or it can be A81 depending on options selected. I have a formula in A79, A80, and A81. I need a way to copy the last cell in this column, but it is not the last cell in the whole column of the sheet. Is there a way to define a range I'm looking for the last used cell??

I'm trying to make a quote page where options can make the final price come out on A79 or A81, then the final value cell needs to be copied and pasted to a different cell with in the sheet.

I cant get the first part to work correctly.

Sub FinalAdder()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet


'Selecting the quote sheet
ActiveWorkbook.Sheets("Location Quote").Select

'supposed to find the last used cell after A78, before the next blank cell
Sheets("Location Quote").Range("A78").End(xlDown).Copy

'Pasting previous cell value to the next blank cell above J93
Sheets("Location Quote").Range("J93").End(xlUp).Offset(1).PasteSpecial xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

 

[MARK858]

And what is the formula in the cells?

 

[chrismarek9]

All the cells have if statements like this:

=IF('Sales Configurator'!Q5="Yes","Total Price Before Discount",IF(AND('Sales Configurator'!Q5="No",'Sales Configurator'!Q6>0),"Total Price Before Discount",IF(AND('Sales Configurator'!Q5="No",'Sales Configurator'!Q6=0,'Sales Configurator'!Q7=TRUE),"Total Price Before Discount",'Sales Configurator'!H12&" Total Price")))

 

[Joe4]

If it is really just two cells, why not just check them, something like.

[COLOR=#333333]If Sheets("Location Quote").Range("A79") <> 0 Then
   InvAmt = [/COLOR][COLOR=#333333]Sheets("Location Quote").Range("A79")
Else
[/COLOR][COLOR=#333333]   InvAmt = [/COLOR][COLOR=#333333]Sheets("Location Quote").Range("A81")
End If[/COLOR]

 

[chrismarek9]

All the cells have if statements like this:

=IF('Sales Configurator'!Q5="Yes","Total Price Before Discount",IF(AND('Sales Configurator'!Q5="No",'Sales Configurator'!Q6>0),"Total Price Before Discount",IF(AND('Sales Configurator'!Q5="No",'Sales Configurator'!Q6=0,'Sales Configurator'!Q7=TRUE),"Total Price Before Discount",'Sales Configurator'!H12&" Total Price")))

But the last 2 cells have another statement adding an option that if not met the just "" for the cell...making some of the cells blank.

If A79 has total price then you will not have anything but "" for A80 and A81.
If A81 has total price then A80 & A79 will have text displaying discount and price before discount.
I want to copy total price each time and have it transferred to a different cell.

 

[chrismarek9]

I'm new to excel, what does InvAmt exaclty do?

 

[Joe4]

It is just a random variable I am using to "capture" the Invoice Amount (I am capturing it instead of Copying it).

You could then use it to assign to your other range, i.e.

[COLOR=#333333]Sheets("Location Quote").Range("J93").End(xlUp).Offset(1) = InvAmt
[/COLOR]

Either way should work.

 

[chrismarek9]

Gotcha!

So I modified the code but may not be following correctly:

Sub Te()
If Sheets("Location Quote").Range("A81") <> 0 Then
InvAmt = Sheets("Location Quote").Range("A81")
Else
InvAmt = Sheets("Location Quote").Range("A79")
End If

End Sub



This should read: If A81 does not equal 0 or blank then set InvAmt = A81
otherwise set InvAmt equal to A79

for some reason every time there is no value in A81 it doesn't take A79 it just uses ""