Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
what type of information we can enter into spreadsheet cells? | New Users to Excel | |||
boxes with information to enter | Excel Discussion (Misc queries) | |||
How do I make someone enter information in a cell | Excel Discussion (Misc queries) | |||
Is there a way to hide worksheets and/or rows/columns based on information enter into a particular cell of range of cells? | New Users to Excel | |||
selecting current row information and transfering it. | Excel Programming |