Return value of cell next to empty cell

[jkate]

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.

 

[MintJulep]

Range.Rows.Count - Range.Row gives you the row number of the last row of the range.

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"

 

[davidbeach]

I think you'd be looking for a "not empty".

 

[SkipVought]

Hi,

Assuming a Range Name of PONumber...

'
   [PONumber].Value = [B1].End(xlDown).Offset(1, -1).Value

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jkate]

Thanks.

What if the two column array is on another worksheet in the workbook?

 

[SkipVought]

'
   [PONumber].Value = Worksheets("Your sheet name").[B1].End(xlDown).Offset(1, -1).Value

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jkate]

This seems to work, but I get an error. It doesn't like .Rows

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

 

[Denial]

JKate, I'm surprised your macro got past its orphaned "End With".

 

[jkate]

It didn't. I didn't have the most recent code in my last post.

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

 

[SkipVought]

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 Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[SkipVought]

You ought not use Activate & Select indiscriminately. It is very inefficient.

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jkate]

Ok. Thanks for your help. Everything seems to work except for 1 thing. The code is note placing the value of Range("A" & lastRow + 1).Value into cell PONo on worksheet POLog. Below is the code. The code is more than I've posted before because it needs to preform other functions like clearing the PO Form.


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

 

[SkipVought]

 Range("PONo") = [highlight #FCE94F]Worksheets("POLog").[/highlight]Range("A" & lastRow + 1).Value

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}

 

[jkate]

That did it Skip! Thanks so much!

 

[SkipVought]

I'd also disambiguate ALL the sheet references.

Sub DisplayForm()

    Dim lastRow As Long, NextItem, PORowStart, i
    Dim wsSUM As Worksheet
    
    Set wsSUM = ActiveSheet     '[b]I would prefer a specific sheet name here[/b]
    
    PORowStart = 13             '[b]this ought to be derived[/b]
    
    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       '[b]Which sheet???[/b]
    
    '    ProductSelection.Show
        wsSUM.Range("POnumber").Value = wsSUM.Range("PONumber2").Value
    End With
End Sub

Skip,

_{Just traded in my OLD subtlety...
for a NUance!}