Macro:- Find first blank cell and write text

[Mac0001UK]

Hi All,
I have a spreadsheet in 9 Columns (A to I) and, at the moment, 772 Rows.
I want to write a macro that:-
Finds the first blank cell in column B, (ie B772)
Writes some text into that cell,
Steps to the next cell on the right, (ie C772)
Writes some text into that cell,
Steps to the next cell on the right, (ie D772)
Writes some text into that cell,
Steps across 2 cells to the right, (ie F772)
Writes some text into that cell,
Steps across 3 cells to the right and 1 down, (ie I773)
Writes some text into that cell.
End the macro.

The text being written to the cells will be the same on all occasions.

Can anyone help pleaese, many thanks.

 

[Jacob Skaria]

Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE
using short-key Alt+F11. Insert a module and paste the below code. Save. Get
back to Workbook. Tools|Macro|Run MacroTest(). Please try and feedback

Sub MacroTest()

Dim strData As String
Dim lngLastRow As Long

strData = "Some text"
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1

Range("B" & lngLastRow) = strData
Range("C" & lngLastRow) = strData
Range("D" & lngLastRow) = strData
Range("F" & lngLastRow) = strData
Range("I" & lngLastRow + 1) = strData

End Sub

 

[Jacob Skaria]

Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE
using short-key Alt+F11. Insert a module and paste the below code. Save. Get
back to Workbook. Tools|Macro|Run MacroTest(). Please try and feedback

Sub MacroTest()

Dim strData As String
Dim lngLastRow As Long

strData = "Some text"
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1

Range("B" & lngLastRow) = strData
Range("C" & lngLastRow) = strData
Range("D" & lngLastRow) = strData
Range("F" & lngLastRow) = strData
Range("I" & lngLastRow + 1) = strData

End Sub

 

[Don Guillett]

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)

 

[Don Guillett]

You may like this. You don't say what text you need to input for each cell.
Sub putindata()
With Cells(Cells(Rows.Count, "a").End(xlUp).Row + 1, "a")
.Value = "aaa"
.Offset(, 1) = "bbb"
.Offset(, 2) = "ccc"
.Offset(, 3) = "dd"
.Offset(, 5) = "ff"
.Offset(1, 8) = "i773"
End With
End Sub

 

[Don Guillett]

You may like this. You don't say what text you need to input for each cell.
Sub putindata()
With Cells(Cells(Rows.Count, "a").End(xlUp).Row + 1, "a")
.Value = "aaa"
.Offset(, 1) = "bbb"
.Offset(, 2) = "ccc"
.Offset(, 3) = "dd"
.Offset(, 5) = "ff"
.Offset(1, 8) = "i773"
End With
End Sub

 

[Mac0001UK]

--
Mac Macdonald

Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE
using short-key Alt+F11. Insert a module and paste the below code. Save. Get
back to Workbook. Tools|Macro|Run MacroTest(). Please try and feedback

Sub MacroTest()

Dim strData As String
Dim lngLastRow As Long

strData = "Some text"
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1

Range("B" & lngLastRow) = strData
Range("C" & lngLastRow) = strData
Range("D" & lngLastRow) = strData
Range("F" & lngLastRow) = strData
Range("I" & lngLastRow + 1) = strData

End Sub

Jacob,

Many thanks. Your solution works beautifully, so simple, so elegant, when
you know how, I have a lot to learn.

Cheers,
Mac

 

[Mac0001UK]

--
Mac Macdonald

Set the Security level to low/medium in (Tools|Macro|Security). 'Launch VBE
using short-key Alt+F11. Insert a module and paste the below code. Save. Get
back to Workbook. Tools|Macro|Run MacroTest(). Please try and feedback

Sub MacroTest()

Dim strData As String
Dim lngLastRow As Long

strData = "Some text"
lngLastRow = ActiveSheet.Cells(Rows.Count, "B").End(xlUp).Row + 1

Range("B" & lngLastRow) = strData
Range("C" & lngLastRow) = strData
Range("D" & lngLastRow) = strData
Range("F" & lngLastRow) = strData
Range("I" & lngLastRow + 1) = strData

End Sub

Jacob,

Many thanks. Your solution works beautifully, so simple, so elegant, when
you know how, I have a lot to learn.

Cheers,
Mac