Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
last cell on row
Hello
Ive been trying desparately to accomplish the following: I need to check for a value in a row on a cell by cell basis. Columns B,C,F,G may or may not have a value in them. If B is empty, then clicking on a commandbutton places the caption text into the cell and moves to column C; Clicking on a command button again places its caption text into that cell and jumps to Col F; again clicking the command button places the caption text in the cell in Column F and again with G. When those cells on that row are filled, I then need to have excel find the last row + 1 to begin it all over again. Column A has a formula in it down to row 60 and in the 2nd row (A2:K2) have column heading in them. Columns D, E, H, I are all hidden columns with formulas. How can I accomplish this? Ive tried : Range("B65536").End(xlUp).Offset(1, 0), but If column B has a value and C does not, then the offset brings it down another row. Im thinking that I should be using xlRight somehow, but I can't seem to get my head around it. Could anyone shine a little light for me? Thank you so much Terry V |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
last cell on row
So you always fill the row from B to C to F to G.
Option Explicit Sub testme01() Dim DestCell As Range Dim myCols As Range Dim HowManyFilled As Long With Worksheets("sheet1") Set myCols = .Range("b:c,f:g") Set DestCell = .Cells(.Rows.Count, "B").End(xlUp) HowManyFilled = Application.CountA(Intersect(DestCell.EntireRow, myCols)) Select Case HowManyFilled Case Is = 4: Set DestCell = DestCell.Offset(1, 0) 'down a row Case Is 1: Set DestCell = DestCell.Offset(0, HowManyFilled + 2) Case Else: Set DestCell = DestCell.Offset(0, HowManyFilled) End Select End With MsgBox DestCell.Address DestCell.Value = "whatever you wanted" End Sub Another way would just be to check to see if those cells are filled (C,F,G on the same row as the B cell): Option Explicit Sub testme01A() Dim DestCell As Range With Worksheets("sheet1") Set DestCell = .Cells(.Rows.Count, "B").End(xlUp) With DestCell If IsEmpty(DestCell.Offset(0, 1)) Then Set DestCell = DestCell.Offset(0, 1) ElseIf IsEmpty(DestCell.Offset(0, 4)) Then Set DestCell = DestCell.Offset(0, 4) ElseIf IsEmpty(DestCell.Offset(0, 5)) Then Set DestCell = DestCell.Offset(0, 5) Else Set DestCell = DestCell.Offset(1, 0) End If End With End With MsgBox DestCell.Address DestCell.Value = "whatever you wanted" End Sub Terry V wrote: Hello Ive been trying desparately to accomplish the following: I need to check for a value in a row on a cell by cell basis. Columns B,C,F,G may or may not have a value in them. If B is empty, then clicking on a commandbutton places the caption text into the cell and moves to column C; Clicking on a command button again places its caption text into that cell and jumps to Col F; again clicking the command button places the caption text in the cell in Column F and again with G. When those cells on that row are filled, I then need to have excel find the last row + 1 to begin it all over again. Column A has a formula in it down to row 60 and in the 2nd row (A2:K2) have column heading in them. Columns D, E, H, I are all hidden columns with formulas. How can I accomplish this? Ive tried : Range("B65536").End(xlUp).Offset(1, 0), but If column B has a value and C does not, then the offset brings it down another row. Im thinking that I should be using xlRight somehow, but I can't seem to get my head around it. Could anyone shine a little light for me? Thank you so much Terry V -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
last cell on row
And I might have used the .end(xltoright) if there had not been a gap in your
columns. Terry V wrote: Hello Ive been trying desparately to accomplish the following: I need to check for a value in a row on a cell by cell basis. Columns B,C,F,G may or may not have a value in them. If B is empty, then clicking on a commandbutton places the caption text into the cell and moves to column C; Clicking on a command button again places its caption text into that cell and jumps to Col F; again clicking the command button places the caption text in the cell in Column F and again with G. When those cells on that row are filled, I then need to have excel find the last row + 1 to begin it all over again. Column A has a formula in it down to row 60 and in the 2nd row (A2:K2) have column heading in them. Columns D, E, H, I are all hidden columns with formulas. How can I accomplish this? Ive tried : Range("B65536").End(xlUp).Offset(1, 0), but If column B has a value and C does not, then the offset brings it down another row. Im thinking that I should be using xlRight somehow, but I can't seem to get my head around it. Could anyone shine a little light for me? Thank you so much Terry V -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
last cell on row
Wow,
Thats so cool. Thank you so much; not only did you help me with the particular problem, but I learned a fair amount from your code. Thank you so much Terry V "Dave Peterson" wrote in message ... So you always fill the row from B to C to F to G. Option Explicit Sub testme01() Dim DestCell As Range Dim myCols As Range Dim HowManyFilled As Long With Worksheets("sheet1") Set myCols = .Range("b:c,f:g") Set DestCell = .Cells(.Rows.Count, "B").End(xlUp) HowManyFilled = Application.CountA(Intersect(DestCell.EntireRow, myCols)) Select Case HowManyFilled Case Is = 4: Set DestCell = DestCell.Offset(1, 0) 'down a row Case Is 1: Set DestCell = DestCell.Offset(0, HowManyFilled + 2) Case Else: Set DestCell = DestCell.Offset(0, HowManyFilled) End Select End With MsgBox DestCell.Address DestCell.Value = "whatever you wanted" End Sub Another way would just be to check to see if those cells are filled (C,F,G on the same row as the B cell): Option Explicit Sub testme01A() Dim DestCell As Range With Worksheets("sheet1") Set DestCell = .Cells(.Rows.Count, "B").End(xlUp) With DestCell If IsEmpty(DestCell.Offset(0, 1)) Then Set DestCell = DestCell.Offset(0, 1) ElseIf IsEmpty(DestCell.Offset(0, 4)) Then Set DestCell = DestCell.Offset(0, 4) ElseIf IsEmpty(DestCell.Offset(0, 5)) Then Set DestCell = DestCell.Offset(0, 5) Else Set DestCell = DestCell.Offset(1, 0) End If End With End With MsgBox DestCell.Address DestCell.Value = "whatever you wanted" End Sub Terry V wrote: Hello Ive been trying desparately to accomplish the following: I need to check for a value in a row on a cell by cell basis. Columns B,C,F,G may or may not have a value in them. If B is empty, then clicking on a commandbutton places the caption text into the cell and moves to column C; Clicking on a command button again places its caption text into that cell and jumps to Col F; again clicking the command button places the caption text in the cell in Column F and again with G. When those cells on that row are filled, I then need to have excel find the last row + 1 to begin it all over again. Column A has a formula in it down to row 60 and in the 2nd row (A2:K2) have column heading in them. Columns D, E, H, I are all hidden columns with formulas. How can I accomplish this? Ive tried : Range("B65536").End(xlUp).Offset(1, 0), but If column B has a value and C does not, then the offset brings it down another row. Im thinking that I should be using xlRight somehow, but I can't seem to get my head around it. Could anyone shine a little light for me? Thank you so much Terry V -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing Options | Excel Discussion (Misc queries) | |||
Code to copy the formulae of one cell to all the cell in the rangewith the specific cell and columnnumber changing | Excel Discussion (Misc queries) | |||
Populate a cell if values in cell 1 and cell 2 match cell 3 and 4 | Excel Worksheet Functions | |||
How to create/run "cell A equals Cell B put Cell C info in Cell D | Excel Discussion (Misc queries) | |||
Question: Cell formula or macro to write result of one cell to another cell | Excel Programming |