Selecting Cells to enter information
 

I have a macro that needs to tab over and enter information 4 different times
per row of information. This happens after it finds the first blank cell in
a column. Because the number of records change I can not tell it to select
cel "b4" then enter the information select "d4" enter the info. etc. I need
to know how to tell the macro to tab over "x" number of times. I also need
it to use the enter key after the row.

What is the programming for this?
--
Thank you for your time.
Windows XP
Office 2002

Selecting Cells to enter information
 

I'm not sure what you want to do but from what you say, the offset method
will work. This is an offset from the current cell.

Also you need a loop in your macro to loop through the rows looking for
blank cells. But the information is a little sketchy.


--
HTHs Martin


"James Kendall" wrote:

I have a macro that needs to tab over and enter information 4 different times
per row of information. This happens after it finds the first blank cell in
a column. Because the number of records change I can not tell it to select
cel "b4" then enter the information select "d4" enter the info. etc. I need
to know how to tell the macro to tab over "x" number of times. I also need
it to use the enter key after the row.

What is the programming for this?
--
Thank you for your time.
Windows XP
Office 2002

Selecting Cells to enter information
 

Ok. I'll try again. My macro does this.

Sub Formatting()

Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
Range("A1").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveWorkbook.Save

End Sub


Before the "ActiveWorkbook.Save" I need to tab to the B column cell and
enter "N/A", tab to the C column cell and enter 41, tab to the E column cell
and enter "N/A", tab to the K column cell and enter "N/A", then have the
selected cell go to the next row cell in column A (like pressing enter when
manually entering information). This happens 3 times (the number 41 is the
only info that changes).

The Offset sounds like it might work. I have to figure that out.
--
Thank you for your time.
Windows XP
Office 2002


"Martin Fishlock" wrote:

I'm not sure what you want to do but from what you say, the offset method
will work. This is an offset from the current cell.

Also you need a loop in your macro to loop through the rows looking for
blank cells. But the information is a little sketchy.


--
HTHs Martin


"James Kendall" wrote:

I have a macro that needs to tab over and enter information 4 different times
per row of information. This happens after it finds the first blank cell in
a column. Because the number of records change I can not tell it to select
cel "b4" then enter the information select "d4" enter the info. etc. I need
to know how to tell the macro to tab over "x" number of times. I also need
it to use the enter key after the row.

What is the programming for this?
--
Thank you for your time.
Windows XP
Office 2002

Selecting Cells to enter information
 

James:

You need something like the following, but there is a problem with stopping
it.
You need a stopper in the find.


Sub Formatting()
Dim c As Variant
Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
Range("A1").Select
Set c = Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Do While TypeName(c) = "Range"
Cells(c.Row, 2) = "N/A"
Cells(c.Row, 3) = 41
Cells(c.Row, 5) = "N/A"
Cells(c.Row, 11) = "N/A"
Cells(c.Row + 1, 1).Select
If c.Row = 65535 Then Exit Do
Set c = Cells.FindNext(ActiveCell).Select
' need a stopper in here for the end of the values try:
Loop
Set c = Nothing
ActiveWorkbook.Save

End Sub

--
HTHs Martin


"James Kendall" wrote:

Ok. I'll try again. My macro does this.

Sub Formatting()

Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
Range("A1").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate
ActiveWorkbook.Save

End Sub


Before the "ActiveWorkbook.Save" I need to tab to the B column cell and
enter "N/A", tab to the C column cell and enter 41, tab to the E column cell
and enter "N/A", tab to the K column cell and enter "N/A", then have the
selected cell go to the next row cell in column A (like pressing enter when
manually entering information). This happens 3 times (the number 41 is the
only info that changes).

The Offset sounds like it might work. I have to figure that out.
--
Thank you for your time.
Windows XP
Office 2002


"Martin Fishlock" wrote:

I'm not sure what you want to do but from what you say, the offset method
will work. This is an offset from the current cell.

Also you need a loop in your macro to loop through the rows looking for
blank cells. But the information is a little sketchy.


--
HTHs Martin


"James Kendall" wrote:

I have a macro that needs to tab over and enter information 4 different times
per row of information. This happens after it finds the first blank cell in
a column. Because the number of records change I can not tell it to select
cel "b4" then enter the information select "d4" enter the info. etc. I need
to know how to tell the macro to tab over "x" number of times. I also need
it to use the enter key after the row.

What is the programming for this?
--
Thank you for your time.
Windows XP
Office 2002

Selecting Cells to enter information
 

You pointed me in the right direction. After some modification here is the
final outcome. <I really need to get my boss to allow me training in code

Sub Formatting()
Dim c As Variant
Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False,
FieldInfo _
:=Array(1, 2), TrailingMinusNumbers:=True
Range("A1").Select
Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext,
MatchCase:= _
False, SearchFormat:=False).Activate

Set c = ActiveCell
Cells(c.Row, 2) = "N/A"
Cells(c.Row, 3) = 41
Cells(c.Row, 5) = "N/A"
Cells(c.Row, 11) = "N/A"
Cells(c.Row + 1, 1).Select
Set c = ActiveCell
Cells(c.Row, 2) = "N/A"
Cells(c.Row, 3) = 42
Cells(c.Row, 5) = "N/A"
Cells(c.Row, 11) = "N/A"
Cells(c.Row + 1, 1).Select
Set c = ActiveCell
Cells(c.Row, 2) = "N/A"
Cells(c.Row, 3) = 43
Cells(c.Row, 5) = "N/A"
Cells(c.Row, 11) = "N/A"
Cells(c.Row + 1, 1).Select
Set c = Nothing
ActiveWorkbook.Save

End Sub


I had a problem with the code line:

Set c = Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate

For some reason it would not take, hence the change to Set c= ActiveCell.
It works now and that is what matters. Thanks.
--
Thank you for your time.
Windows XP
Office 2002


"Martin Fishlock" wrote:

James:

You need something like the following, but there is a problem with stopping
it.
You need a stopper in the find.


Sub Formatting()
Dim c As Variant
Columns("K:K").Select
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Selection.TextToColumns Destination:=Range("K1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False,
Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, _
FieldInfo:=Array(1, 2), TrailingMinusNumbers:=True
Range("A1").Select
Set c = Cells.Find(What:="", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
Do While TypeName(c) = "Range"
Cells(c.Row, 2) = "N/A"
Cells(c.Row, 3) = 41
Cells(c.Row, 5) = "N/A"
Cells(c.Row, 11) = "N/A"
Cells(c.Row + 1, 1).Select
If c.Row = 65535 Then Exit Do
Set c = Cells.FindNext(ActiveCell).Select
' need a stopper in here for the end of the values try:
Loop
Set c = Nothing
ActiveWorkbook.Save

End Sub

--
HTHs Martin