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