Cant get Macro to go to next empty row

[nealtd]

Hey everyone, I have created a macro to copy information from one workbook and paste in another. I'm putting in If statements and the only problem is that I cannot figure out how to get the macro to go to the next empty row if the condition is met for both of the if statements. Please help me out.

Private Sub Transfer_Click()
Dim SubmissionID As String
Dim BatchName As String
Dim DateReviewed As Date
Dim RetrievalAssociate As String
Dim DoesVolumeMatch As String
Dim WasCorrectMediaAttached As String
Dim WasPDFNamedCorrectly As String
Dim myData As Workbook

Worksheets("sheet1").Select
SubmissionID = Range("a4")
Worksheets("sheet1").Select
BatchName = Range("b4")
Worksheets("sheet1").Select
DateReviewed = Range("d4")
Worksheets("sheet1").Select
RetrievalAssociate = Range("e4")
Worksheets("sheet1").Select
DoesVolumeMatch = Range("H4")
Worksheets("sheet1").Select
WasCorrectMediaAttached = Range("I4")
Worksheets("sheet1").Select
WasPDFNamedCorrectly = Range("J4")

Set myData = Workbooks.Open("H:\0 MediaValidationFormTest.xlsm")
Worksheets("Account_Details").Select
Worksheets("Account_Details").Range("a4").Select
RowCount = Worksheets("Account_Details").Range("a4").CurrentRegion.Rows.Count
With Worksheets("Account_Details").Range("a4")
.Offset(ColumnCount + 3, 2) = SubmissionID
.Offset(ColumnCount + 2, 2) = BatchName
.Offset(ColumnCount + 0, 2) = DateReviewed
.Offset(ColumnCount + 1, 2) = RetrievalAssociate
If WasCorrectMediaAttached = "No" Then
.Offset(ColumnCount + 9, 1) = "Was the correct media attached?"
Else
If DoesVolumeMatch = "No" Then
.Offset(ColumnCount + 9, 1) = "Does Volume match spreadsheet total?"
Else
Exit Sub 'do nothing
End If
End If
End With
myData.Save
End Sub

 

[wesborland1234]

Get the next empty row like this:

Dim emptyRow As Integer
        emptyRow = Range("A" & rows.Count).End(xlUp).Row + 1

 

[nealtd]

Thank you for the response. I tried that and the macro is still doing the same thing. It only copies over one instead of both.

 

[dmt32]

Hi,
You have what appears to be two undeclared variables:

RowCount – this you initialize but do not use?
ColumnCount – Is this variable initialized elsewhere?

Unless declared elsewhere, always a good idea to place at Top of a Module Option Explicit – this will ensure that all variables used are declared.

To assist in your main question – you could probably use a bit of indexing so if both conditions are meet you increment the offset +1

I have played with you code a little & removed all the select statements which are unnecessary – it’s untested but may give you something to work with.

 Option Explicit
Private Sub Transfer_Click()


    Dim SubmissionID As String, BatchName As String
    Dim RetrievalAssociate As String, DoesVolumeMatch As String
    Dim WasCorrectMediaAttached As String, WasPDFNamedCorrectly As String
    Dim RowCount As Long, ColumnCount As Long
    Dim i As Integer
    Dim DateReviewed As Date
    Dim myData As Workbook
    
    With ThisWorkbook.Worksheets("sheet1")
        SubmissionID = .Range("a4")
        BatchName = .Range("b4")
        DateReviewed = .Range("d4")
        RetrievalAssociate = .Range("e4")
        DoesVolumeMatch = .Range("H4")
        WasCorrectMediaAttached = .Range("I4")
        WasPDFNamedCorrectly = .Range("J4")
    End With


    Set myData = Workbooks.Open("H:\0 MediaValidationFormTest.xlsm")
    
    With Worksheets("Account_Details").Range("a4")
        RowCount = .CurrentRegion.Rows.Count
        .Offset(ColumnCount + 3, 2) = SubmissionID
        .Offset(ColumnCount + 2, 2) = BatchName
        .Offset(ColumnCount + 0, 2) = DateReviewed
        .Offset(ColumnCount + 1, 2) = RetrievalAssociate
        
        If WasCorrectMediaAttached = "No" Then .Offset(ColumnCount + 9, 1) = "Was the correct media attached?": i = 1
        If DoesVolumeMatch = "No" Then .Offset(ColumnCount + 9 + i, 1) = "Does Volume match spreadsheet total?"
            
    End With
    myData.Save
End Sub

The default of variable i is 0 (zero) - if First condition is met it increments + 1. This variable is used in the second condition to increase the offset.

Hopefully, it will do what you want but adjust as required.



Dave

 

[nealtd]

I appreciate your help, but that doesn't quite do what I want the macro to do. I want to be able to put "No" for either condition and the macro copy over to a different sheet. Also I want to be able to add additional columns and just copy over the if statement.

 

[dmt32]

I appreciate your help, but that doesn't quite do what I want the macro to do. I want to be able to put "No" for either condition and the macro copy over to a different sheet. Also I want to be able to add additional columns and just copy over the if statement.

Solution provided I am certain met your original request for assistance:

I'm putting in If statements and the only problem is that I cannot figure out how to get the macro to go to the next empty row if the condition is met for both of the if statements.

there was no mention in your first post of placing this data to sheet other than one included in your code or anything about additional columns so I could not have know this.

I adjusted your code based on first post and did say at the end you should adjust as it required.

Dave

 

[nealtd]

I apologize for my lack of information in my first post. The help that you gave me did resolve my issue that I initially asked about. I appreciate it.

 

[nealtd]

Hey,

This is what I have now. Still isn't quite working. I'm not getting anything from my If statements when I run the macro. Please let me know if you have any suggestions. Thank you in advance.

Private Sub Transfer_Click()Dim SubmissionID As String
Dim BatchName As String
Dim DateReviewed As Date
Dim RetrievalAssociate As String
Dim DoesVolumeMatch As String
Dim WasCorrectMediaAttached As String
Dim WasPDFNamedCorrectly As String
Dim myData As Workbook
Dim emptyrow As String


Worksheets("sheet1").Select
SubmissionID = Range("a4")
BatchName = Range("b4")
DateReviewed = Range("d4")
RetrievalAssociate = Range("e4")
DoesVolumeMatch = Range("j4")
WasCorrectMediaAttached = Range("I4")
WasPDFNamedCorrectly = Range("k4")


Set myData = Workbooks.Open("C:\Users\nealt\OneDrive\Documents\Validation")
Worksheets("Account_Details").Select
Worksheets("Account_Details").Range("a4").Select
emptyrow = Range("a" & Rows.Count).End(xlUp).Offset(1, 0)
RowCount = Worksheets("Account_Details").Range("a4").CurrentRegion.Rows.Count
With Worksheets("Account_Details").Range("a4")
.Offset(RowCount + 3, 2) = SubmissionID
.Offset(RowCount + 2, 2) = BatchName
.Offset(RowCount + 0, 2) = DateReviewed
.Offset(RowCount + 1, 2) = RetrievalAssociate
If WasCorrectMediaAttached = "No" Then
emptyrow = "Was the correct media attached?"
If DoesVolumeMatch = "No" Then
emptyrow = "Does Volume match spreadsheet total?"
Exit Sub 'do nothing
End If
End If
End With
myData.Save
End Sub

 

[Caleeco]

Hello,

This is untested, as I havent recreated a dataset. But try this:

Private Sub Transfer_Click()
Dim SubmissionID As String
Dim BatchName As String
Dim DateReviewed As Date
Dim RetrievalAssociate As String
Dim DoesVolumeMatch As String
Dim WasCorrectMediaAttached As String
Dim WasPDFNamedCorrectly As String
Dim myData As Workbook
Dim emptyrow As long


With Worksheets("Sheet1")
    SubmissionID = Range("a4")
    BatchName = Range("b4")
    DateReviewed = Range("d4")
    RetrievalAssociate = Range("e4")
    DoesVolumeMatch = Range("j4")
    WasCorrectMediaAttached = Range("I4")
    WasPDFNamedCorrectly = Range("k4")
End With


Set myData = Workbooks.Open("C:\Users\nealt\OneDrive\Documents\Validation")


Worksheets("Account_Details").Range("a4").Select
emptyrow = Range("a" & Rows.Count).End(xlUp).Offset(1, 0).Row


RowCount = Worksheets("Account_Details").Range("a4").CurrentRegion.Rows.Count
With Worksheets("Account_Details").Range("a4")
    .Offset(RowCount + 3, 2) = SubmissionID
    .Offset(RowCount + 2, 2) = BatchName
    .Offset(RowCount + 0, 2) = DateReviewed
    .Offset(RowCount + 1, 2) = RetrievalAssociate
If WasCorrectMediaAttached = "No" Then
    Range("A" & emptyrow) = "Was the correct media attached?"
ElseIf DoesVolumeMatch = "No" Then
    Range("A" & emptyrow) = "Does Volume match spreadsheet total?"
End If


End With
myData.Save
End Sub

You had defined 'emptyrow' as a string. I've changed it to a Long variable, and slightly modified your if statements!

Let me know if it produced the correct results.
Thanks
Caleeco

 

[nealtd]

Hello,

This is untested, as I havent recreated a dataset. But try this:

Private Sub Transfer_Click()
Dim SubmissionID As String
Dim BatchName As String
Dim DateReviewed As Date
Dim RetrievalAssociate As String
Dim DoesVolumeMatch As String
Dim WasCorrectMediaAttached As String
Dim WasPDFNamedCorrectly As String
Dim myData As Workbook
Dim emptyrow As long


With Worksheets("Sheet1")
    SubmissionID = Range("a4")
    BatchName = Range("b4")
    DateReviewed = Range("d4")
    RetrievalAssociate = Range("e4")
    DoesVolumeMatch = Range("j4")
    WasCorrectMediaAttached = Range("I4")
    WasPDFNamedCorrectly = Range("k4")
End With


Set myData = Workbooks.Open("C:\Users\nealt\OneDrive\Documents\Validation")


Worksheets("Account_Details").Range("a4").Select
emptyrow = Range("a" & Rows.Count).End(xlUp).Offset(1, 0).Row


RowCount = Worksheets("Account_Details").Range("a4").CurrentRegion.Rows.Count
With Worksheets("Account_Details").Range("a4")
    .Offset(RowCount + 3, 2) = SubmissionID
    .Offset(RowCount + 2, 2) = BatchName
    .Offset(RowCount + 0, 2) = DateReviewed
    .Offset(RowCount + 1, 2) = RetrievalAssociate
If WasCorrectMediaAttached = "No" Then
    Range("A" & emptyrow) = "Was the correct media attached?"
ElseIf DoesVolumeMatch = "No" Then
    Range("A" & emptyrow) = "Does Volume match spreadsheet total?"
End If


End With
myData.Save
End Sub

You had defined 'emptyrow' as a string. I've changed it to a Long variable, and slightly modified your if statements!

Let me know if it produced the correct results.
Thanks
Caleeco

Hi,

I just tried this. When I run the macro it copies only one of the IFs and it copies it on the same page, not on the new workbook.