VBA code revision (reference different cell if blank)

[Lil Stinker]

Total noob at VBA so apologies up front.
I have this code to automatically enter details to an invoice record.

Sub QuoteRecord()

Dim qtno As String
Dim custname As String
Dim projname As String
Dim amt As Currency
Dim dt_issue As Date
Dim dt_due As Date
Dim nextrec As Range

qtno = Range("K1")
custname = Range("B3")
projname = Range("B2")
amt = Range("K40")
dt_issue = Range("K4")
dt_due = Range("E9")

Set nextrec = Sheet4.Range("A1048576").End(xlUp).Offset(1, 0)

nextrec = qtno
nextrec.Offset(0, 1) = custname
nextrec.Offset(0, 2) = projname
nextrec.Offset(0, 3) = amt
nextrec.Offset(0, 4) = dt_issue
nextrec.Offset(0, 5) = dt_due


End Sub

Problem is, I need to reference J1 & K1 for the full quote number (qtno). My quote number has letters in J1 and numbers in K1. K1 increments with each new quote. J1's initials change with each user.
Second problem, I need dt_due to reference E9 but when E9 is empty, I need it to reference E27.

 

[nemmi69]

If I have this right

qtno = nextrec.Offset(0, 10) & nextrec.Offset(0, 11) ' This concatenates the two cells to make the string

for conditional question

If IsEmpty(Range("E9").Value)=True Then ' If empty take vale E27 else E9
    dt_due = E27
Else
    dt_due = E9
End If

 

[Lil Stinker]

Please bare with me and thanks for responding. The VBA code for conditional IF Then variable works great, thank you! However, I'm getting "Run-time error '91': Object variable or With block variable not set" when I plug in the qtno code.

On the form, the full quote number is in cells J1 & K1.

I would like to concatenate this into column A on the record sheet automatically. Currently, it is only referencing K1 and resulting in 7024 on the record rather than BS7024.

I'm not sure how to reference two cells in VBA. I tried qtno = Range("J1:K1") but it still only refers to one cell or the other.

Does that help?

 

[nemmi69]

try

Set nextrec = Sheets(4).Range("A1:A" & Sheets(4).Range("A" & Rows.Count).End(xlUp).Row)

to concatenate try

qtno = Range("J1").value & Range("K1").value

 

[Lil Stinker]

The concatenate code worked!

The VBA code however resulted in no record being recorded for some reason. When I switched it back to the original code "Set nextrec = Sheet4.Range("A1048576").End(xlUp).Offset(1, 0)", it worked with the quote number concatenated.

Thanks for your help!