Return value of cell next to empty cell
Return value of cell next to empty cell
(OP)
I need some help with VBA code to look at a two column array (columns A&B) called POLog, find the last empty cell in cell Bx (with "x" being the row) and return the value that's in cell Ax. I then want the value from Ax to be placed in a cell called "PONumber". Can someone help me with that?
Thanks.
Thanks.





RE: Return value of cell next to empty cell
Loop backwards through rows in column B until you find an "empty".
Get the value from the corresponding cell in column A and set that as the value of "PONumber"
RE: Return value of cell next to empty cell
RE: Return value of cell next to empty cell
Assuming a Range Name of PONumber...
CODE
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Return value of cell next to empty cell
What if the two column array is on another worksheet in the workbook?
RE: Return value of cell next to empty cell
CODE
' [PONumber].Value = Worksheets("Your sheet name").[B1].End(xlDown).Offset(1, -1).ValueSkip,
Just traded in my OLD subtlety...
for a NUance!
RE: Return value of cell next to empty cell
Sub PONumber()
Dim lastRow As Long
Worksheets(POLog).Activate
lastRow = Cells(.Rows.Count, "B").End(xlUp).Row
End With
Range("PONumber2").Value = lastRow + 1
PONo = Range("A" & lastRow + 1).Value
Range("PONo").Value = PONo
End Sub
RE: Return value of cell next to empty cell
RE: Return value of cell next to empty cell
Below is the current code, but I still get an error. It doesn't like .Rows. When I run the macro, .Rows comes back highlighted in blue.
Sub PONumber()
Dim lastRow As Long
Worksheets("POLog").Activate
lastRow = .Cells(.Rows.Count, "b").End(xlUp).Row
Range("PONumber2") = Range("A" & lastRow + 1).Value
End Sub
RE: Return value of cell next to empty cell
CODE
Sub PONumber() Dim lastRow As Long With Worksheets(POLog) lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row End With Range("PONumber2").Value = lastRow + 1 Range("PONo").Value = Cells(lastrow + 1, "A").Value End SubSkip,
Just traded in my OLD subtlety...
for a NUance!
RE: Return value of cell next to empty cell
Skip,
Just traded in my OLD subtlety...
for a NUance!
RE: Return value of cell next to empty cell
Sub DisplayForm()
Dim lastRow As Long
With Worksheets("POLog")
lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row
End With
Range("PONumber2").Value = lastRow + 1
Range("PONo") = Range("A" & lastRow + 1).Value
Range("POnumber").Value = ""
Range("SubTotal").Value = 0
NextItem = Range("NextItem").Value
PORowStart = 13
Range("B" & PORowStart + 1, "M" & PORowStart + 1).ClearContents
If NextItem > 2 Then
For i = (PORowStart + (NextItem - 1)) To (PORowStart + 2) Step -1
Rows(i).EntireRow.Delete
Next i
End If
Range("A1").Value = 1
ProductSelection.Show
Range("POnumber").Value = Range("PONumber2").Value
End Sub
RE: Return value of cell next to empty cell
CODE
Range("PONo") = Worksheets("POLog").Range("A" & lastRow + 1).ValueSkip,
Just traded in my OLD subtlety...
for a NUance!
RE: Return value of cell next to empty cell
RE: Return value of cell next to empty cell
CODE
Sub DisplayForm() Dim lastRow As Long, NextItem, PORowStart, i Dim wsSUM As Worksheet Set wsSUM = ActiveSheet 'I would prefer a specific sheet name here PORowStart = 13 'this ought to be derived With Worksheets("POLog") lastRow = .Cells(.Rows.Count, "B").End(xlUp).Row wsSUM.Range("PONumber2").Value = lastRow + 1 wsSUM.Range("PONo") = .Range("A" & lastRow + 1).Value wsSUM.Range("POnumber").Value = "" wsSUM.Range("SubTotal").Value = 0 NextItem = wsSUM.Range("NextItem").Value .Range(.Range("B" & PORowStart + 1), .Range("M" & PORowStart + 1)).ClearContents If NextItem > 2 Then For i = (PORowStart + (NextItem - 1)) To (PORowStart + 2) Step -1 .Rows(i).EntireRow.Delete Next i End If Range("A1").Value = 1 'Which sheet??? ' ProductSelection.Show wsSUM.Range("POnumber").Value = wsSUM.Range("PONumber2").Value End With End SubSkip,
Just traded in my OLD subtlety...
for a NUance!