If a cell is filled up, copy data to the right of cell instead

[aaronph]

Hello fellas, would appreciate if you guys could help me out here. I have this bunch of code here to save to column B. But Im looking for code to allow me to save the data to column C if B is filled up. And to D if B and C is filled up and so on..


Sub QuotationSave()

Sheets("Database").Select

Range("B1").Formula = "=CONCATENATE($B$2 & "" "" & $B$3 & "" "" & Quote!$H$7)"
Range("B2").Formula = "=Quote!$H$6"
Range("B3").Formula = "=Quote!$D$18"
Range("B4").Formula = "=Quote!$H$5"
Range("B5").Formula = "=Quote!$H$7"
Range("B6").Formula = "=Quote!$C$12"
Range("B7").Formula = "=Quote!$C$9"
Range("B8").Formula = "=Quote!$H$42"
Range("B9").Formula = "=CONCATENATE(Quote!$C$21 & "" | "" & Quote!$C$22 & "" | "" & Quote!$C$23 & "" | "" & Quote!$C$24 & "" | "" & Quote!$C$25 & "" | "" & Quote!$C$26 & "" | "" & Quote!$C$27 & "" | "" & Quote!$C$28 & "" | "" & Quote!$C$29 & "" | "" & Quote!$C$30 & "" | "" & Quote!$C$31 & "" | "" & Quote!$C$32 & "" | "" & Quote!$C$33 & "" | "" & Quote!$C$34 & "" | "" & Quote!$C$35 & "" | "" & Quote!$C$36)"
Range("B10").Formula = "=IFERROR(VLOOKUP(""Adjustment"",Quote!$C$21:$H$36,3,0),""Not Applicable"")"
Range("B11").Formula = "=Quote!$C$9"
Range("B12").Formula = "=Now()"
Range("B15").Formula = "=IF(B14<>"""",""Y"",""N"")"

Range("B1:B15").Select
Selection.Copy

Range("B1:B15").PasteSpecial xlPasteValues

Sheets("Quote").Select

MsgBox "Quotation is now in our records."

End Sub

Thank you in advance for your time!

Aaron

[Bob Phillips]

Do you want t go to C if any of B1:B15 is full, or for each individually.

[aaronph]

I'd like to go to C if any cell of the range B1:B15 is not blank.

Thank you in advance!

[Bob Phillips]

Sub QuotationSave()
Dim colnum As Long

With Sheets("Database")

colnum = IIf(.Range("B1").CurrentRegion.Address = "$B$1", 2, .Range("B1").CurrentRegion.Columns.Count + 2)

.Cells(1, colnum).FormulaR1C1 = "=CONCATENATE(R2C2 & "" "" & R3C2 & "" "" & Quote!R7C8)"
.Cells(2, colnum).Formula = "=Quote!$H$6"
.Cells(3, colnum).Formula = "=Quote!$D$18"
.Cells(4, colnum).Formula = "=Quote!$H$5"
.Cells(5, colnum).Formula = "=Quote!$H$7"
.Cells(6, colnum).Formula = "=Quote!$C$12"
.Cells(7, colnum).Formula = "=Quote!$C$9"
.Cells(8, colnum).Formula = "=Quote!$H$42"
.Cells(9, colnum).Formula = "=CONCATENATE(Quote!$C$21&"" | ""&Quote!$C$22&"" | ""&Quote!$C$23&"" | ""& " & _
"Quote!$C$24&"" | ""&Quote!$C$25&"" | ""&Quote!$C$26&"" | ""& " & _
"Quote!$C$27&"" | ""&Quote!$C$28&"" | ""&Quote!$C$29&"" | ""& " & _
"Quote!$C$30&"" | ""&Quote!$C$31&"" | ""&Quote!$C$32&"" | ""& " & _
"Quote!$C$33&"" | ""&Quote!$C$34&"" | ""&Quote!$C$35&"" | "" & Quote!$C$36)"
.Cells(10, colnum).Formula = "=IFERROR(VLOOKUP(""Adjustment"",Quote!$C$21:$H$36,3,0),""Not Applicable"")"
.Cells(11, colnum).Formula = "=Quote!$C$9"
.Cells(12, colnum).Formula = "=Now()"
.Cells(15, colnum).FormulaR1C1 = "=IF(R[-1]C2<>"""",""Y"",""N"")"

With .Cells(1, colnum).Resize(, 15)

.Value = .Value
End With

MsgBox "Quotation is now in our records."
End With
End Sub

[aaronph]

Hi XLD,

Thanks for the earlier help.

I noticed the data is saved in Column D when Column B and C is blank using the code above. I actually require to store data in Column B. If Column B is already filled up by an earlier data, data is automated to save in Column C. And if C is filled up, then D and so on and so forth.

15606

Appreciate much if you could assist further. Thank you!

[Bob Phillips]

It starts it B for me, then fills C, then D, etc.

You must have something in B1 and C1.

[aaronph]

I've deleted the columns B, C and D and run the code, it saves in D still.

[Bob Phillips]

Post your workbook.