A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Macro:- Find first blank cell and write text



 
 
Thread Tools Display Modes
  #1  
Old May 16th, 2009, 04:59 AM posted to microsoft.public.excel.worksheet.functions
Mac0001UK
external usenet poster
 
Posts: 7
Default 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  
Old May 16th, 2009, 05:31 AM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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

  #4  
Old May 16th, 2009, 01:55 PM posted to microsoft.public.excel.worksheet.functions
Don Guillett
external usenet poster
 
Posts: 6,167
Default Macro:- Find first blank cell and write text

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
Microsoft MVP Excel
SalesAid Software

"Mac0001UK" wrote in message
...
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


  #5  
Old May 17th, 2009, 02:34 AM posted to microsoft.public.excel.worksheet.functions
Mac0001UK
external usenet poster
 
Posts: 7
Default 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

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 11:06 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.