Cant get Macro to go to next empty row

nealtd

New Member
Joined
May 20, 2016
Messages
19
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
 
the final IF is conditional... and output to the same cell. Only one will be output, if they are both true, one will be overwritten. If this is not the case, could you please clarify what the if statement is meant to do?

Am I correct in saying, all you want to do is move this data:
Code:
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

To the account details sheet, with these offsets?
Code:
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

Regards
Caleeco
 
Upvote 0
the final IF is conditional... and output to the same cell. Only one will be output, if they are both true, one will be overwritten. If this is not the case, could you please clarify what the if statement is meant to do?

Am I correct in saying, all you want to do is move this data:
Code:
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

To the account details sheet, with these offsets?
Code:
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

Regards
Caleeco

What I'm creating is a macro for work. I want employees to be able to copy over what errors they locate onto one spreadsheet and if an error is located (in which it will say no on the sheet) by running the macro it will fill out the form telling what the error was. I want it to be able to show as many errors that are found. The items in a4, b4, d4, and e4 have to be put into the form every time, but the rest are conditional to if the sheet says no. Sorry for not clarifying enough. I'm completely new to this.
 
Upvote 0
Do you know how I could do this without the IFs overwriting each other? Where is both conditions are met the next IF will go to the next row.
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top