How to add data to the last empty column in VBA-CodePudding

I am trying to get the last column with cell data saved as an integer to a variable and then use that variable to insert string to the rows below, then go to the next column without data.

Here is the code I am using:

Option Explicit

Sub Macro3()

Dim LastCol As Integer

LastCol = sheets("Results").Cells(1, Columns.Count).End(xlToLeft).Column

Cells(1, LastCol).Value = "Worksheet Name"
Cells(2, LastCol).Value = "Maximum"
Cells(3, LastCol).Value = "Minimum"
Cells(4, LastCol).Value = "%ML"
Cells(1, LastCol).Offset(0, 1) = "sheet 1"
Cells(2, LastCol).Offset(0, 1) = "15"
Cells(3, LastCol).Offset(0, 1) = "13"
Cells(4, LastCol).Offset(0, 1) = "2"

End Sub

Except when I run this code multiple times it comes out like this:

I am aiming for this:

Does Anyone have a suggestion on where I am going wrong, please?

CodePudding user response：

You're overwriting the values in the last column.

LastCol = sheets("Results").Cells(1, Columns.Count).End(xlToLeft).Column

This returns the last populated column in row 1, or just 1 if no cell in row 1 is populated.

Use something like this:

Dim LastCol As Long
LastCol = sheets("Results").Cells(1, Columns.Count).End(xlToLeft).Column

If Not IsEmpty(sheets("Results").Cells(1, LastCol)) Then
   LastCol = LastCol   1
End If

CodePudding user response：

Try something like this:

For LastCol = 1 To 10000
    If Len(ActiveWorkbook.Worksheets("Results").Cells(1, LastCol)) = 0 Then Exit For
Next LastCol