If statement for entire column

[woogewoogewooge]

Does anyone know how I can put my IF's so I wont what to put this formula in 300 times. I have it setup where individually every IF statement will paste to the next row on another workbook. I want to repeat this process but with SubmissionID = A4:A300, BatchName = B4:B300, etc. so that I don't have to put the formula almost 300 times. And I want the macro to paste to the next row for every cell in the column. Please help if you can.

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 AccountNumber As String
Dim DateRequested As Date

Worksheets("sheet1").Select
SubmissionID = Range("a4")
BatchName = Range("b4")
AccountNumber = Range("c4")
DateRequested = Range("d4")
DateReviewed = Range("f4")
RetrievalAssociate = Range("g4")
DoesVolumeMatch = Range("j4")
WasCorrectMediaAttached = Range("k4")
WasPDFNamedCorrectly = Range("l4")
AppRedacted = Range("m4")
AdditionalAccount = Range("n4")
ResultsColumn = Range("o4")
SystemRecord = Range("p4")
FolderName = Range("q4")
SheetStructure = Range("r4")
Other = Range("s4")
Notes = Range("t4")

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 DoesVolumeMatch = "No" Then
.Offset(RowCount + 6, 1) = "Does volume match spreadsheet total?"
.Offset(RowCount + 6, 2) = AccountNumber
.Offset(RowCount + 6, 3) = DateRequested
.Offset(RowCount + 6, 4) = "No"
.Offset(RowCount + 6, 5) = "Yes"
End If
If WasCorrectMediaAttached = "No" Then
.Offset(RowCount - 3, 1) = "Was the correct media attached?"
.Offset(RowCount - 3, 2) = AccountNumber
.Offset(RowCount - 3, 3) = DateRequested
.Offset(RowCount - 3, 4) = "No"
.Offset(RowCount - 3, 5) = "Yes"
End If
If WasPDFNamedCorrectly = "No" Then
.Offset(RowCount - 2, 1) = "Was PDF named correctly?"
.Offset(RowCount - 2, 2) = AccountNumber
.Offset(RowCount - 2, 3) = DateRequested
.Offset(RowCount - 2, 4) = "No"
.Offset(RowCount - 2, 5) = "Yes"
End If
If AppRedacted = "No" Then
.Offset(RowCount - 1, 1) = "Was application redacted correctly?"
.Offset(RowCount - 1, 2) = AccountNumber
.Offset(RowCount - 1, 3) = DateRequested
.Offset(RowCount - 1, 4) = "No"
.Offset(RowCount - 1, 5) = "Yes"
End If
If AdditionalAccount = "No" Then
.Offset(RowCount - 0, 1) = "Were additional media/account numbers requested?"
.Offset(RowCount - 0, 2) = AccountNumber
.Offset(RowCount - 0, 3) = DateRequested
.Offset(RowCount - 0, 4) = "No"
.Offset(RowCount - 0, 5) = "Yes"
End If
If ResultsColumn = "No" Then
.Offset(RowCount + 1, 1) = "Was results column on spreadsheet correct?"
.Offset(RowCount + 1, 2) = AccountNumber
.Offset(RowCount + 1, 3) = DateRequested
.Offset(RowCount + 1, 4) = "No"
.Offset(RowCount + 1, 5) = "Yes"
End If
If SystemRecord = "No" Then
.Offset(RowCount + 2, 1) = "Was the System of Record documented correctly?"
.Offset(RowCount + 2, 2) = AccountNumber
.Offset(RowCount + 2, 3) = DateRequested
.Offset(RowCount + 2, 4) = "No"
.Offset(RowCount + 2, 5) = "Yes"
End If
If FolderName = "No" Then
.Offset(RowCount + 3, 1) = "Did the folder name match the spreadsheet?"
.Offset(RowCount + 3, 2) = AccountNumber
.Offset(RowCount + 3, 3) = DateRequested
.Offset(RowCount + 3, 4) = "No"
.Offset(RowCount + 3, 5) = "Yes"
End If
If SheetStructure = "No" Then
.Offset(RowCount + 4, 1) = "Is spreadsheet structure correct?"
.Offset(RowCount + 4, 2) = AccountNumber
.Offset(RowCount + 4, 3) = DateRequested
.Offset(RowCount + 4, 4) = "No"
.Offset(RowCount + 4, 5) = "Yes"
End If
If Other = "No" Then
.Offset(RowCount + 5, 1) = "Other (Please provide comment)"
.Offset(RowCount + 5, 2) = AccountNumber
.Offset(RowCount + 5, 3) = DateRequested
.Offset(RowCount + 5, 4) = "No"
.Offset(RowCount + 5, 5) = "Yes"
End If
End With

End Sub

 

[Roderick_E]

Ugh, offset -- look into FOR loop

 

[GaryG9595]

Are you trying to add an If statement to multiple columns and rows...?
I looked up something similar where I wanted to add an iferror to multiple columns and rows without going line by line.
This worked for me.
Highlight the columns and rows you want to add the if statement to. Press F5. Click the special button. click formulas, make sure all the boxes are ticked, numbers, text, logicals, and errors. add your if statement to the formula bar. Then hold Control and Enter.
Hope this helps...
Sorry, Long day... Just went back and read your post.... I don't think this will help you

 

[woogewoogewooge]

Thank you, I will look in that

 

[woogewoogewooge]

I understand Gary, thanks.