This is an example of sheet (sheet 2) I have,
A B
10
20
30
40
50
I am trying to hardcode a value, say "Data" to column A in my sheet 2 till the last row of column B so my output will look something like this
A B
Data 10
Data 20
Data 30
Data 40
Data 50
Currently I am using the below code but am getting an error! How to achieve at this.
Sub AddData()
Sheets("Sheet2").Range("A2:A" & LastRow).Value="A"
End Sub
Try this:
Sub AddData()
Dim LastRow As Long
LastRow = Sheets("Sheet2").Cells(Rows.Count, "B").End(xlUp).Row
Sheets("Sheet2").Range("A1:A" & LastRow).Value = "Data"
End Sub
You do not seem to have a value for LastRow. You can find the last row by using:-
Sub AddData()
lastRow = Sheets("Sheet2").Cells(65536, 2).End(xlUp).Row
Sheets("Sheet2").Range("A2:A" & lastRow).Value = "A"
End Sub
Another twist on the same answer ...
Sub AddData()
Dim LastRow As Long
LastRow = Sheets("Sheet4").UsedRange.Rows.Count
Sheets("Sheet4").Range("A2:A" & LastRow).Value = "Data"
End Sub
Sheets("Sheet2").Columns("B").SpecialCells(xlCellTypeConstants, xlNumbers).Offset(, -1) = "Data"
this will put "Data" string in any column "A" cell whose aside colum "B" one contains a number, as per your eexample
but you can vary this "filtering"
for instance:
Sheets("Sheet2").Columns("B").SpecialCells(xlCellTypeConstants).Offset(, -1) = "Data"
will consider all cells in column "B" containing constants (be they numbers or text values)
while:
Sheets("Sheet2").Columns("B").SpecialCells(xlCellTypeFormulas).Offset(, -1) = "Data"
will consider all cells in column "B" containing formulas
see https://msdn.microsoft.com/en-us/library/office/ff196157(v=office.15).aspx
