×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Return value of cell next to empty cell

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.

RE: Return value of cell next to empty cell

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"

RE: Return value of cell next to empty cell

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

RE: Return value of cell next to empty cell

Hi,

Assuming a Range Name of PONumber...

CODE

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Return value of cell next to empty cell

(OP)
Thanks.

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).Value 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Return value of cell next to empty cell

(OP)
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

RE: Return value of cell next to empty cell

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

RE: Return value of cell next to empty cell

(OP)
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

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 Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Return value of cell next to empty cell

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

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Return value of cell next to empty cell

(OP)
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

RE: Return value of cell next to empty cell


CODE

Range("PONo") = Worksheets("POLog").Range("A" & lastRow + 1).Value 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Return value of cell next to empty cell

(OP)
That did it Skip! Thanks so much!

RE: Return value of cell next to empty cell

I'd also disambiguate ALL the sheet references.

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 Sub 

Skip,

glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources