If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Macro:- Find first blank cell and write text
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. -- Mac Macdonald |
#2
|
|||
|
|||
Macro:- Find first blank cell and write text
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 -- If this post helps click Yes --------------- Jacob Skaria "Mac0001UK" wrote: 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. -- Mac Macdonald |
#3
|
|||
|
|||
Macro:- Find first blank cell and write text
|
#5
|
|||
|
|||
Macro:- Find first blank cell and write text
-- Mac Macdonald "Jacob Skaria" wrote: 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 -- If this post helps click Yes --------------- Jacob Skaria "Mac0001UK" wrote: 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. -- Mac Macdonald Jacob, Many thanks. Your solution works beautifully, so simple, so elegant, when you know how, I have a lot to learn. Cheers, Mac |
Thread Tools | |
Display Modes | |
|
|