Find Row or Paste to Next Empty Row

[spydey]

It is late and I think my brain is fried. Any input/assistance is greatly appreciated.

I have some code that looks for a specific value in column A, once found, then it copies other values horizontally in the same row of the located value.
Example: Value is 195263, it is found in Column A in Row 35. It then copies other values to column B, C, D, ..... AJ, all in row 35.

However, I need to add an additional clause where if the value is NOT found in column A, then in the next empty cell in column A, it pastes the value and then copies other values horizontally in the same row.
Example: Value is 195263, it is not in Column A. The next empty cell in Column A is 173, it copies the value (195263) to cell address A173, and then copies other values to column B, C, D, ..... AJ, all in row 173.

Here is the relevant snippet of the code I have been using:

Sub Single_UID

Dim Unique_ID As Long
Dim wbL As Workbook
Dim wsL As Worksheet


Set wbL = Workbooks("Log.xlsm")
Set wsL = wbL.Worksheets("Log")

With wsL
        
    Set Findrow = .Range("Unique_ID").Find(What:=Unique_ID, LookIn:=xlValues, LookAt:=xlWhole)
    If Not Findrow Is Nothing Then
        FirstAddress = Findrow.Address

        Do
            
            Findrow.Offset(, 1).Value = Format(Now(), "mm/dd/yyyy")
            ...
            ...
            ...
            ...
            Findrow.Offset(, 35).Value = Condition
                       
        Exit Do
        
        Set Findrow = .Range("Unique_ID").FindNext(Findrow)
        Loop While Not Findrow Is Nothing And Findrow.Address <> FirstAddress
    End If
End With

End Sub

Any thoughts/ideas/suggestions, on what/how I can adjust the code so if the Unique_ID is not found in Column A, it then goes to the next empty cell in Column A, pastes the Unique_ID, and then copies the other values to B, C, D, .... AJ, all in the same row?

-Spydey

 

[dmt32]

Hi,
untested but try this update to your code & see id does what you want

Sub Single_UID()
    
    Dim Unique_ID   As Long, NextRow As Long
    Dim FindRow     As Range
    Dim wbL         As Workbook
    Dim wsL         As Worksheet
    
    Set wbL = Workbooks("Log.xlsm")
    Set wsL = wbL.Worksheets("Log")
    
    NextRow = wsL.Cells(wsL.Rows.Count, "A").End(xlUp).Row + 1
    Set FindRow = wsL.Range("Unique_ID").Find(What:=Unique_ID, LookIn:=xlValues, LookAt:=xlWhole)
    If FindRow Is Nothing Then Set FindRow = wsL.Cells(NextRow, 1)
    
    FindRow.Offset(, 1).Value = Format(Now(), "mm/dd/yyyy")
    
    FindRow.Offset(, 35).Value = Condition
    
End Sub

Dave

 

[spydey]

Hi,
untested but try this update to your code & see id does what you want

Sub Single_UID()
   
    Dim Unique_ID   As Long, NextRow As Long
    Dim FindRow     As Range
    Dim wbL         As Workbook
    Dim wsL         As Worksheet
   
    Set wbL = Workbooks("Log.xlsm")
    Set wsL = wbL.Worksheets("Log")
   
    NextRow = wsL.Cells(wsL.Rows.Count, "A").End(xlUp).Row + 1
    Set FindRow = wsL.Range("Unique_ID").Find(What:=Unique_ID, LookIn:=xlValues, LookAt:=xlWhole)
    If FindRow Is Nothing Then Set FindRow = wsL.Cells(NextRow, 1)
   
    FindRow.Offset(, 1).Value = Format(Now(), "mm/dd/yyyy")
   
    FindRow.Offset(, 35).Value = Condition
   
End Sub

Dave

Dave,

Thanks for the suggestion. I think that it is getting me part way there but there appear to be a few parts possibly missing, or perhaps I am just not understanding it clearly enough.

The whole Loop has been removed. Was that intentional? Is the Loop needed any longer?

Also, the IF statement doesn't appear to have an ELSE section.

Thoughts?

 

[dmt32]

Dave,

Thanks for the suggestion. I think that it is getting me part way there but there appear to be a few parts possibly missing, or perhaps I am just not understanding it clearly enough.

The whole Loop has been removed. Was that intentional? Is the Loop needed any longer?

Also, the IF statement doesn't appear to have an ELSE section.

Thoughts?

You indicated that value in Column A is unique so I took this to be that the Find function is only looking for one occurrence of the search value in the range & therefore, you would not need the FindNext code.

You don't always need an Else statement and when If statement written as a one liner, you do not need an End If

Part of your code that I was not sure of is how you are populating your Unique_ID variable to perform the search?

Dave

 

[spydey]

You indicated that value in Column A is unique so I took this to be that the Find function is only looking for one occurrence of the search value in the range & therefore, you would not need the FindNext code.

You don't always need an Else statement and when If statement written as a one liner, you do not need an End If

Part of your code that I was not sure of is how you are populating your Unique_ID variable to perform the search?

Dave

Yes, the Unique_ID value will only appear once in Column A. It is either already there, and with the Find function, we can identify the corresponding Row, or it isn't there, and so we need to add it to the next available empty cell in column A.

I am capturing the Unique_ID value from a different workbook.
The code is actually started in that other workbook (a template).
That template workbook has a user-input value, which is the UID.
When the code is started in the template, it opens the log workbook, performs a Find in the Log column A of the UID from the template workbook, etc etc etc.

I am going to adjust my code and get back to you. Thanks for your help so far.

 

[spydey]

@dmt32

Dave,

I tested it and found the following:

• If the UID did not exist, in the Log column A, then it was added to the next empty cell in column A, and the other values were pasted horizontally.
  • Worked great!!
• However, if the UID already existed, then it did nothing. It didn't copy the other values horizontally and just exited the sub.

In looking at the code, I believe the issue stems from not ending the if statement, but I could be wrong.

Basically:

• We set the NextRow value to equal the next empty cell in column A.
• We set the FindRow value to equal the address of the Find function when it searches for the UID.
• If the Find function comes back empty, meaning FindRow is empty, then we set FindRow to equal the NextRow, which is the next empty cell
• We then paste the other values horizontally on the same row as the empty cell.

The issue stems from if the UID is already in column A, then it stops.
It looks to me like the code that performs the horizontal paste is being seen as part of the IF statement.
So if the results of the Find Function are blank, then it performs the horizontal paste, otherwise nothing happens.
I corrected this by adding an End IF. This appears to have corrected the issue.

Let me know what you think:

Sub Single_UID()

Dim Unique_ID As Long
Dim NextRow As Long
Dim FindRow As Range
Dim wbT, wbL As Workbook
Dim wsT, wsL As Worksheet

Set wbT = ThisWorkbook
Set wsT = wbT.Worksheets(1)
Set wbL = Workbooks("Log.xlsm")
Set wsL = wbL.Worksheets("Log")

Application.ScreenUpdating = False

With wsT
    Unique_ID = .Range("Single.Unique_ID").Value
    ...
    Condition = .Range("Single.Condition").Value
End With

wbL.Activate

With wsL
    On Error Resume Next
    .ShowAllData
   
        With .AutoFilter.Sort
            .SortFields.Add Key:=Range("Unique_ID"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
End With
   
    NextRow = wsL.Cells(wsL.Rows.Count, "A").End(xlUp).Row + 1
    Set FindRow = wsL.Range("Unique_ID").Find(What:=Unique_ID, LookIn:=xlValues, LookAt:=xlWhole)
    If FindRow Is Nothing Then
   
        Set FindRow = wsL.Cells(NextRow, 1)
   
    End If

            FindRow.Offset(, 0).Value = Unique_ID
            FindRow.Offset(, 1).Value = Format(Now(), "mm/dd/yyyy")
            ...
            FindRow.Offset(, 35).Value = Condition
       
With wsL
    .ShowAllData
    .Columns("A:AJ").ColumnWidth = 120
    .Rows("1:10002").AutoFit
    .Columns("A:AJ").EntireColumn.AutoFit
   
End With

wbL.Save

End Sub

 

[dmt32]

Hi,
writing the If Statement in a single line like this

If FindRow Is Nothing Then Set FindRow = wsL.Cells(NextRow, 1)

should perform no differently to writing as you have

If FindRow Is Nothing Then
   
        Set FindRow = wsL.Cells(NextRow, 1)
   
    End If

it should do the same thing & not affect the operation of the rest of the code.

I note that since your first post that you have added additional code including On Error Resume Next . This statement allows execution to continue despite any run-time errors that may occur & should only be used in a limited way where you should either pass error handling back to default behaviour of VBA by using On Error Goto 0 or direct error handling to a label like On Error Goto MyErrorLabel
If your code is erroring may explain why you get no results?

Other than this & without seeing your workbook(s) cannot explain further the issue you mention.
If still having problems with it then if possible, place copy of workbook with dummy data in a file sharing site like dropbox & provide a link to it

Dave