Need to make macro smaller

N

nealtd

New Member
Joined
May 20, 2016
Messages
19
I made a macro that is too large to even post on here and will not run in excel because it is too large. Can anyone help me to make this smaller? I'm completely clueless. Took me almost a month to create this just to find out that it is too long. :( I should have been smarter.

Code: 
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")
SubmissionID2 = Range("a5")
BatchName2 = Range("b5")
AccountNumber2 = Range("c5")
DateRequested2 = Range("d5")
DateReviewed2 = Range("f5")
RetrievalAssociate2 = Range("g5")
DoesVolumeMatch2 = Range("j5")
WasCorrectMediaAttached2 = Range("k5")
WasPDFNamedCorrectly2 = Range("l5")
AppRedacted2 = Range("m5")
AdditionalAccount2 = Range("n5")
ResultsColumn2 = Range("o5")
SystemRecord2 = Range("p5")
FolderName2 = Range("q5")
SheetStructure2 = Range("r5")
Other2 = Range("s5")
SubmissionID3 = Range("a6")
BatchName3 = Range("b6")
AccountNumber3 = Range("c6")
DateRequested3 = Range("d6")
DateReviewed3 = Range("f6")
RetrievalAssociate3 = Range("g6")
DoesVolumeMatch3 = Range("j6")
WasCorrectMediaAttached3 = Range("k6")
WasPDFNamedCorrectly3 = Range("l6")
AppRedacted3 = Range("m6")
AdditionalAccount3 = Range("n6")
ResultsColumn3 = Range("o6")
SystemRecord3 = Range("p6")
FolderName3 = Range("q6")
SheetStructure3 = Range("r6")
Other3 = Range("s6")
SubmissionID4 = Range("a7")
BatchName4 = Range("b7")
AccountNumber4 = Range("c7")
DateRequested4 = Range("d7")
DateReviewed4 = Range("f7")
RetrievalAssociate4 = Range("g7")
DoesVolumeMatch4 = Range("j7")
WasCorrectMediaAttached4 = Range("k7")
WasPDFNamedCorrectly4 = Range("l7")
AppRedacted4 = Range("m7")
AdditionalAccount4 = Range("n7")
ResultsColumn4 = Range("o7")
SystemRecord4 = Range("p7")
FolderName4 = Range("q7")
SheetStructure4 = Range("r7")
Other4 = Range("s7")
SubmissionID5 = Range("a8")
BatchName5 = Range("b8")
AccountNumber5 = Range("c8")
DateRequested5 = Range("d8")
DateReviewed5 = Range("f8")
RetrievalAssociate5 = Range("g8")
DoesVolumeMatch5 = Range("j8")
WasCorrectMediaAttached5 = Range("k8")
WasPDFNamedCorrectly5 = Range("l8")
AppRedacted5 = Range("m8")
AdditionalAccount5 = Range("n8")
ResultsColumn5 = Range("o8")
SystemRecord5 = Range("p8")
FolderName5 = Range("q8")
SheetStructure5 = Range("r8")
Other5 = Range("s8")
SubmissionID6 = Range("a9")
BatchName6 = Range("b9")
AccountNumber6 = Range("c9")
DateRequested6 = Range("d9")
DateReviewed6 = Range("f9")
RetrievalAssociate6 = Range("g9")
DoesVolumeMatch6 = Range("j9")
WasCorrectMediaAttached6 = Range("k9")
WasPDFNamedCorrectly6 = Range("l9")
AppRedacted6 = Range("m9")
AdditionalAccount6 = Range("n9")
ResultsColumn6 = Range("o9")
SystemRecord6 = Range("p9")
FolderName6 = Range("q9")
SheetStructure6 = Range("r9")
Other6 = Range("s9")
SubmissionID7 = Range("a10")
BatchName7 = Range("b10")
AccountNumber7 = Range("c10")
DateRequested7 = Range("d10")
DateReviewed7 = Range("f10")
RetrievalAssociate7 = Range("g10")
DoesVolumeMatch7 = Range("j10")
WasCorrectMediaAttached7 = Range("k10")
WasPDFNamedCorrectly7 = Range("l10")
AppRedacted7 = Range("m10")
AdditionalAccount7 = Range("n10")
ResultsColumn7 = Range("o10")
SystemRecord7 = Range("p10")
FolderName7 = Range("q10")
SheetStructure7 = Range("r10")
Other7 = Range("s10")
SubmissionID8 = Range("a11")
BatchName8 = Range("b11")
AccountNumber8 = Range("c11")
DateRequested8 = Range("d11")
DateReviewed8 = Range("f11")
RetrievalAssociate8 = Range("g11")
DoesVolumeMatch8 = Range("j11")
WasCorrectMediaAttached8 = Range("k11")
WasPDFNamedCorrectly8 = Range("l11")
AppRedacted8 = Range("m11")
AdditionalAccount8 = Range("n11")
ResultsColumn8 = Range("o11")
SystemRecord8 = Range("p11")
FolderName8 = Range("q11")
SheetStructure8 = Range("r11")
Other8 = Range("s11")
SubmissionID9 = Range("a12")
BatchName9 = Range("b12")
AccountNumber9 = Range("c12")
DateRequested9 = Range("d12")
DateReviewed9 = Range("f12")
RetrievalAssociate9 = Range("g12")
DoesVolumeMatch9 = Range("j12")
WasCorrectMediaAttached9 = Range("k12")
WasPDFNamedCorrectly9 = Range("l12")
AppRedacted9 = Range("m12")
AdditionalAccount9 = Range("n12")
ResultsColumn9 = Range("o12")
SystemRecord9 = Range("p12")
FolderName9 = Range("q12")
SheetStructure9 = Range("r12")
Other9 = Range("s12")
SubmissionID10 = Range("a13")
BatchName10 = Range("b13")
AccountNumber10 = Range("c13")
DateRequested10 = Range("d13")
DateReviewed10 = Range("f13")
RetrievalAssociate10 = Range("g13")
DoesVolumeMatch10 = Range("j13")
WasCorrectMediaAttached10 = Range("k13")
WasPDFNamedCorrectly10 = Range("l13")
AppRedacted10 = Range("m13")
AdditionalAccount10 = Range("n13")
ResultsColumn10 = Range("o13")
SystemRecord10 = Range("p13")
FolderName10 = Range("q13")
SheetStructure10 = Range("r13")
Other10 = Range("s13")

Both of these go all the way to 250. I only put the first 10 of the top one and 2 of the bottom one.

Code: 
If DoesVolumeMatch3 = "No" Then
.Offset(RowCount + 17, 1) = "Does volume match spreadsheet total?"
.Offset(RowCount + 17, 2) = AccountNumber3
.Offset(RowCount + 17, 3) = DateRequested3
.Offset(RowCount + 17, 4) = "No"
.Offset(RowCount + 17, 5) = "Yes"
End If
If WasCorrectMediaAttached3 = "No" Then
.Offset(RowCount + 18, 1) = "Was the correct media attached?"
.Offset(RowCount + 18, 2) = AccountNumber3
.Offset(RowCount + 18, 3) = DateRequested3
.Offset(RowCount + 18, 4) = "No"
.Offset(RowCount + 18, 5) = "Yes"
End If
If WasPDFNamedCorrectly3 = "No" Then
.Offset(RowCount + 19, 1) = "Was PDF named correctly?"
.Offset(RowCount + 19, 2) = AccountNumber3
.Offset(RowCount + 19, 3) = DateRequested3
.Offset(RowCount + 19, 4) = "No"
.Offset(RowCount + 19, 5) = "Yes"
End If
If AppRedacted3 = "No" Then
.Offset(RowCount + 20, 1) = "Was application redacted correctly?"
.Offset(RowCount + 20, 2) = AccountNumber3
.Offset(RowCount + 20, 3) = DateRequested3
.Offset(RowCount + 20, 4) = "No"
.Offset(RowCount + 20, 5) = "Yes"
End If
If AdditionalAccount3 = "No" Then
.Offset(RowCount + 21, 1) = "Were additional media/account numbers requested?"
.Offset(RowCount + 21, 2) = AccountNumber3
.Offset(RowCount + 21, 3) = DateRequested3
.Offset(RowCount + 21, 4) = "No"
.Offset(RowCount + 21, 5) = "Yes"
End If
If ResultsColumn3 = "No" Then
.Offset(RowCount + 22, 1) = "Was results column on spreadsheet correct?"
.Offset(RowCount + 22, 2) = AccountNumber3
.Offset(RowCount + 22, 3) = DateRequested3
.Offset(RowCount + 22, 4) = "No"
.Offset(RowCount + 22, 5) = "Yes"
End If
If SystemRecord3 = "No" Then
.Offset(RowCount + 23, 1) = "Was the System of Record documented correctly?"
.Offset(RowCount + 23, 2) = AccountNumber3
.Offset(RowCount + 23, 3) = DateRequested3
.Offset(RowCount + 23, 4) = "No"
.Offset(RowCount + 23, 5) = "Yes"
End If
If FolderName3 = "No" Then
.Offset(RowCount + 24, 1) = "Did the folder name match the spreadsheet?"
.Offset(RowCount + 24, 2) = AccountNumber3
.Offset(RowCount + 24, 3) = DateRequested3
.Offset(RowCount + 24, 4) = "No"
.Offset(RowCount + 24, 5) = "Yes"
End If
If SheetStructure3 = "No" Then
.Offset(RowCount + 25, 1) = "Is spreadsheet structure correct?"
.Offset(RowCount + 25, 2) = AccountNumber3
.Offset(RowCount + 25, 3) = DateRequested3
.Offset(RowCount + 25, 4) = "No"
.Offset(RowCount + 25, 5) = "Yes"
End If
If Other3 = "No" Then
.Offset(RowCount + 26, 1) = "Other (Please provide comment)"
.Offset(RowCount + 26, 2) = AccountNumber3
.Offset(RowCount + 26, 3) = DateRequested3
.Offset(RowCount + 26, 4) = "No"
.Offset(RowCount + 26, 5) = "Yes"
End If
If DoesVolumeMatch4 = "No" Then
.Offset(RowCount + 27, 1) = "Does volume match spreadsheet total?"
.Offset(RowCount + 27, 2) = AccountNumber4
.Offset(RowCount + 27, 3) = DateRequested4
.Offset(RowCount + 27, 4) = "No"
.Offset(RowCount + 27, 5) = "Yes"
End If
If WasCorrectMediaAttached4 = "No" Then
.Offset(RowCount + 28, 1) = "Was the correct media attached?"
.Offset(RowCount + 28, 2) = AccountNumber4
.Offset(RowCount + 28, 3) = DateRequested4
.Offset(RowCount + 28, 4) = "No"
.Offset(RowCount + 28, 5) = "Yes"
End If
If WasPDFNamedCorrectly4 = "No" Then
.Offset(RowCount + 29, 1) = "Was PDF named correctly?"
.Offset(RowCount + 29, 2) = AccountNumber4
.Offset(RowCount + 29, 3) = DateRequested4
.Offset(RowCount + 29, 4) = "No"
.Offset(RowCount + 29, 5) = "Yes"
End If
If AppRedacted4 = "No" Then
.Offset(RowCount + 30, 1) = "Was application redacted correctly?"
.Offset(RowCount + 30, 2) = AccountNumber4
.Offset(RowCount + 30, 3) = DateRequested4
.Offset(RowCount + 30, 4) = "No"
.Offset(RowCount + 30, 5) = "Yes"
End If
If AdditionalAccount4 = "No" Then
.Offset(RowCount + 31, 1) = "Were additional media/account numbers requested?"
.Offset(RowCount + 31, 2) = AccountNumber4
.Offset(RowCount + 31, 3) = DateRequested4
.Offset(RowCount + 31, 4) = "No"
.Offset(RowCount + 31, 5) = "Yes"
End If
If ResultsColumn4 = "No" Then
.Offset(RowCount + 32, 1) = "Was results column on spreadsheet correct?"
.Offset(RowCount + 32, 2) = AccountNumber4
.Offset(RowCount + 32, 3) = DateRequested4
.Offset(RowCount + 32, 4) = "No"
.Offset(RowCount + 32, 5) = "Yes"
End If
If SystemRecord4 = "No" Then
.Offset(RowCount + 33, 1) = "Was the System of Record documented correctly?"
.Offset(RowCount + 33, 2) = AccountNumber4
.Offset(RowCount + 33, 3) = DateRequested4
.Offset(RowCount + 33, 4) = "No"
.Offset(RowCount + 33, 5) = "Yes"
End If
If FolderName4 = "No" Then
.Offset(RowCount + 34, 1) = "Did the folder name match the spreadsheet?"
.Offset(RowCount + 34, 2) = AccountNumber4
.Offset(RowCount + 34, 3) = DateRequested4
.Offset(RowCount + 34, 4) = "No"
.Offset(RowCount + 34, 5) = "Yes"
End If
If SheetStructure4 = "No" Then
.Offset(RowCount + 35, 1) = "Is spreadsheet structure correct?"
.Offset(RowCount + 35, 2) = AccountNumber4
.Offset(RowCount + 35, 3) = DateRequested4
.Offset(RowCount + 35, 4) = "No"
.Offset(RowCount + 35, 5) = "Yes"
End If
If Other4 = "No" Then
.Offset(RowCount + 36, 1) = "Other (Please provide comment)"
.Offset(RowCount + 36, 2) = AccountNumber4
.Offset(RowCount + 36, 3) = DateRequested4
.Offset(RowCount + 36, 4) = "No"
.Offset(RowCount + 36, 5) = "Yes"
End If

If you have any ideas it would be greatly appreciated. Thank you in advance.
 
Sort by date Sort by votes
neatld,

A month !! Yikes! I wouldn't know whether to laugh or cry. I'm doing my best not to laugh.

You need to be reducing that code by employing variables and letting your code loop through your data set.

Give me a little time and I will post example that to the best of my understanding might suit your needs.
 
Upvote 0
Ok. Her is some revised code that hopefully illustrates how you might loop through your data using Offset.
There are a few comments to aid understanding the principle. It is based on my interpretation of your posted code and may not be perfect working code.
I'm sure that with a better understanding of your data and needs that the second part of the code could be further reduced.

Code: 
Worksheets("sheet1").Select


'assign the starting ranges to named variables
Set SubmissionID = Range("a4")
Set BatchName = Range("b4")
Set AccountNumber = Range("c4")
Set DateRequested = Range("d4")
Set DateReviewed = Range("f4")
Set RetrievalAssociate = Range("g4")
Set DoesVolumeMatch = Range("j4")
Set WasCorrectMediaAttached = Range("k4")
Set WasPDFNamedCorrectly = Range("l4")
Set AppRedacted = Range("m4")
Set AdditionalAccount = Range("n4")
Set ResultsColumn = Range("o4")
Set SystemRecord = Range("p4")
Set FolderName = Range("q4")
Set SheetStructure = Range("r4")
Set Other = Range("s4")


'lets assume a set number of rows of data >> 250  (we can determine actual rows if data is dynamic)
' oset is our variable used to specify a row offset from our starting ranges (cells)
'starting cell has a row offset of 0  last cell is offset from starting by 249 (250-1)
'Start a For loop
For oset = 0 To 249 '....do the following




'*********
'***Not sure what your intermediate code may be to determine RowCount and set the range that the RowCount offset is applied to etc but..
'*********


' for example..




If DoesVolumeMatch.Offset(oset, 0) = "No" Then
.Offset(RowCount + 17 + oset, 1) = "Does volume match spreadsheet total?"
.Offset(RowCount + 17 + oset, 2) = AccountNumber
.Offset(RowCount + 17 + oset, 3) = DateRequested
.Offset(RowCount + 17 + oset, 4) = "No"
.Offset(RowCount + 17 + oset, 5) = "Yes"
End If
If WasCorrectMediaAttached = "No" Then
.Offset(RowCount + 18 + oset, 1) = "Was the correct media attached?"
.Offset(RowCount + 18 + oset, 2) = AccountNumber
.Offset(RowCount + 18 + oset, 3) = DateRequested
.Offset(RowCount + 18 + oset, 4) = "No"
.Offset(RowCount + 18 + oset, 5) = "Yes"
End If
If WasPDFNamedCorrectly = "No" Then
.Offset(RowCount + 19 + oset, 1) = "Was PDF named correctly?"
.Offset(RowCount + 19 + oset, 2) = AccountNumber
.Offset(RowCount + 19 + oset, 3) = DateRequested
.Offset(RowCount + 19 + oset, 4) = "No"
.Offset(RowCount + 19 + oset, 5) = "Yes"
End If
If AppRedacted = "No" Then
.Offset(RowCount + 20 + oset, 1) = "Was application redacted correctly?"
.Offset(RowCount + 20 + oset, 2) = AccountNumber
.Offset(RowCount + 20 + oset, 3) = DateRequested
.Offset(RowCount + 20 + oset, 4) = "No"
.Offset(RowCount + 20 + oset, 5) = "Yes"
End If
If AdditionalAccount = "No" Then
.Offset(RowCount + 21 + oset, 1) = "Were additional media/account numbers requested?"
.Offset(RowCount + 21 + oset, 2) = AccountNumber
.Offset(RowCount + 21 + oset, 3) = DateRequested
.Offset(RowCount + 21 + oset, 4) = "No"
.Offset(RowCount + 21 + oset, 5) = "Yes"
End If
If ResultsColumn3 = "No" Then
.Offset(RowCount + 22 + oset, 1) = "Was results column on spreadsheet correct?"
.Offset(RowCount + 22 + oset, 2) = AccountNumber
.Offset(RowCount + 22 + oset, 3) = DateRequested
.Offset(RowCount + 22 + oset, 4) = "No"
.Offset(RowCount + 22 + oset, 5) = "Yes"
End If
If SystemRecord3 = "No" Then
.Offset(RowCount + 23 + oset, 1) = "Was the System of Record documented correctly?"
.Offset(RowCount + 23 + oset, 2) = AccountNumber
.Offset(RowCount + 23 + oset, 3) = DateRequested
.Offset(RowCount + 23 + oset, 4) = "No"
.Offset(RowCount + 23 + oset, 5) = "Yes"
End If
If FolderName3 = "No" Then
.Offset(RowCount + 24 + oset, 1) = "Did the folder name match the spreadsheet?"
.Offset(RowCount + 24 + oset, 2) = AccountNumber
.Offset(RowCount + 24 + oset, 3) = DateRequested
.Offset(RowCount + 24 + oset, 4) = "No"
.Offset(RowCount + 24 + oset, 5) = "Yes"
End If
If SheetStructure3 = "No" Then
.Offset(RowCount + 25 + oset, 1) = "Is spreadsheet structure correct?"
.Offset(RowCount + 25 + oset, 2) = AccountNumber
.Offset(RowCount + 25 + oset, 3) = DateRequested
.Offset(RowCount + 25 + oset, 4) = "No"
.Offset(RowCount + 25 + oset, 5) = "Yes"
End If
If Other3 = "No" Then
.Offset(RowCount + 26 + oset, 1) = "Other (Please provide comment)"
.Offset(RowCount + 26 + oset, 2) = AccountNumber
.Offset(RowCount + 26 + oset, 3) = DateRequested
.Offset(RowCount + 26 + oset, 4) = "No"
.Offset(RowCount + 26 + oset, 5) = "Yes"
End If


'loop for next offset
Next oset

Hope in the first instance that that is at least a little enlightening.
 
Upvote 0
Apologies, forgot to edit certain bits in second part...

Code: 
Worksheets("sheet1").Select


'assign the starting ranges to named variables
Set SubmissionID = Range("a4")
Set BatchName = Range("b4")
Set AccountNumber = Range("c4")
Set DateRequested = Range("d4")
Set DateReviewed = Range("f4")
Set RetrievalAssociate = Range("g4")
Set DoesVolumeMatch = Range("j4")
Set WasCorrectMediaAttached = Range("k4")
Set WasPDFNamedCorrectly = Range("l4")
Set AppRedacted = Range("m4")
Set AdditionalAccount = Range("n4")
Set ResultsColumn = Range("o4")
Set SystemRecord = Range("p4")
Set FolderName = Range("q4")
Set SheetStructure = Range("r4")
Set Other = Range("s4")


'lets assume a set number of rows of data >> 250  (we can determine actual rows if data is dynamic)
' oset is our variable used to specify a row offset from our starting ranges (cells)
'starting cell has a row offset of 0  last cell is offset from starting by 249 (250-1)
'Start a For loop
For oset = 0 To 249 '....do the following




'*********
'***Not sure what your intermediate code may be to determine RowCount and set the range that the RowCount offset is applied to etc but..
'*********


' for example..




If DoesVolumeMatch.Offset(oset, 0) = "No" Then
.Offset(RowCount + 17 + oset, 1) = "Does volume match spreadsheet total?"
.Offset(RowCount + 17 + oset, 2) = AccountNumber
.Offset(RowCount + 17 + oset, 3) = DateRequested
.Offset(RowCount + 17 + oset, 4) = "No"
.Offset(RowCount + 17 + oset, 5) = "Yes"
End If
If WasCorrectMediaAttached.Offset(oset, 0) = "No" Then
.Offset(RowCount + 18 + oset, 1) = "Was the correct media attached?"
.Offset(RowCount + 18 + oset, 2) = AccountNumber
.Offset(RowCount + 18 + oset, 3) = DateRequested
.Offset(RowCount + 18 + oset, 4) = "No"
.Offset(RowCount + 18 + oset, 5) = "Yes"
End If
If WasPDFNamedCorrectly.Offset(oset, 0) = "No" Then
.Offset(RowCount + 19 + oset, 1) = "Was PDF named correctly?"
.Offset(RowCount + 19 + oset, 2) = AccountNumber
.Offset(RowCount + 19 + oset, 3) = DateRequested
.Offset(RowCount + 19 + oset, 4) = "No"
.Offset(RowCount + 19 + oset, 5) = "Yes"
End If
If AppRedacted.Offset(oset, 0) = "No" Then
.Offset(RowCount + 20 + oset, 1) = "Was application redacted correctly?"
.Offset(RowCount + 20 + oset, 2) = AccountNumber
.Offset(RowCount + 20 + oset, 3) = DateRequested
.Offset(RowCount + 20 + oset, 4) = "No"
.Offset(RowCount + 20 + oset, 5) = "Yes"
End If
If AdditionalAccount.Offset(oset, 0) = "No" Then
.Offset(RowCount + 21 + oset, 1) = "Were additional media/account numbers requested?"
.Offset(RowCount + 21 + oset, 2) = AccountNumber
.Offset(RowCount + 21 + oset, 3) = DateRequested
.Offset(RowCount + 21 + oset, 4) = "No"
.Offset(RowCount + 21 + oset, 5) = "Yes"
End If
If ResultsColumn.Offset(oset, 0) = "No" Then
.Offset(RowCount + 22 + oset, 1) = "Was results column on spreadsheet correct?"
.Offset(RowCount + 22 + oset, 2) = AccountNumber
.Offset(RowCount + 22 + oset, 3) = DateRequested
.Offset(RowCount + 22 + oset, 4) = "No"
.Offset(RowCount + 22 + oset, 5) = "Yes"
End If
If SystemRecord.Offset(oset, 0) = "No" Then
.Offset(RowCount + 23 + oset, 1) = "Was the System of Record documented correctly?"
.Offset(RowCount + 23 + oset, 2) = AccountNumber
.Offset(RowCount + 23 + oset, 3) = DateRequested
.Offset(RowCount + 23 + oset, 4) = "No"
.Offset(RowCount + 23 + oset, 5) = "Yes"
End If
If FolderName.Offset(oset, 0) = "No" Then
.Offset(RowCount + 24 + oset, 1) = "Did the folder name match the spreadsheet?"
.Offset(RowCount + 24 + oset, 2) = AccountNumber
.Offset(RowCount + 24 + oset, 3) = DateRequested
.Offset(RowCount + 24 + oset, 4) = "No"
.Offset(RowCount + 24 + oset, 5) = "Yes"
End If
If SheetStructure.Offset(oset, 0) = "No" Then
.Offset(RowCount + 25 + oset, 1) = "Is spreadsheet structure correct?"
.Offset(RowCount + 25 + oset, 2) = AccountNumber
.Offset(RowCount + 25 + oset, 3) = DateRequested
.Offset(RowCount + 25 + oset, 4) = "No"
.Offset(RowCount + 25 + oset, 5) = "Yes"
End If
If Other.Offset(oset, 0) = "No" Then
.Offset(RowCount + 26 + oset, 1) = "Other (Please provide comment)"
.Offset(RowCount + 26 + oset, 2) = AccountNumber
.Offset(RowCount + 26 + oset, 3) = DateRequested
.Offset(RowCount + 26 + oset, 4) = "No"
.Offset(RowCount + 26 + oset, 5) = "Yes"
End If


'loop for next offset
Next oset

Hopefully no other glaring oversights.
 
Upvote 0
Here is my full code with your adjustments. I'm getting "Compile error: Invalid qualifier" for DoesVolumeMatch.Offset(oset, 0). Im still playing with it trying to see if I cant figure it out.

Code: 
Private Sub Transfer_Click()Dim SubmissionID As String
Dim BatchName As String
Dim AccountNumber As String
Dim DateRequested As String
Dim DateReviewed As Date
Dim RetrievalAssociate As String
Dim DoesVolumeMatch As String
Dim WasCorrectMediaAttached As String
Dim WasPDFNamedCorrectly As String
Dim AppRedacted As String
Dim AdditionalAccount As String
Dim ResultsColumn As String
Dim SystemRecord As String
Dim FolderName As String
Dim SheetStructure As String
Dim Other As String
Dim myData As Workbook


Worksheets("sheet1").Select




'assign the starting ranges to named variables
 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")




'lets assume a set number of rows of data >> 250  (we can determine actual rows if data is dynamic)
' oset is our variable used to specify a row offset from our starting ranges (cells)
'starting cell has a row offset of 0  last cell is offset from starting by 249 (250-1)
'Start a For loop
For oset = 0 To 249 '....do the following




Set myData = Workbooks.Open("C:\Users\nealt\OneDrive\Documents\Validation")
Worksheets("Account_Details").Select
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 DoesVolumeMatch.Offset(oset, 0) = "No" Then
.Offset(RowCount + 17 + oset, 1) = "Does volume match spreadsheet total?"
.Offset(RowCount + 17 + oset, 2) = AccountNumber
.Offset(RowCount + 17 + oset, 3) = DateRequested
.Offset(RowCount + 17 + oset, 4) = "No"
.Offset(RowCount + 17 + oset, 5) = "Yes"
End If
If WasCorrectMediaAttached.Offset(oset, 0) = "No" Then
.Offset(RowCount + 18 + oset, 1) = "Was the correct media attached?"
.Offset(RowCount + 18 + oset, 2) = AccountNumber
.Offset(RowCount + 18 + oset, 3) = DateRequested
.Offset(RowCount + 18 + oset, 4) = "No"
.Offset(RowCount + 18 + oset, 5) = "Yes"
End If
If WasPDFNamedCorrectly.Offset(oset, 0) = "No" Then
.Offset(RowCount + 19 + oset, 1) = "Was PDF named correctly?"
.Offset(RowCount + 19 + oset, 2) = AccountNumber
.Offset(RowCount + 19 + oset, 3) = DateRequested
.Offset(RowCount + 19 + oset, 4) = "No"
.Offset(RowCount + 19 + oset, 5) = "Yes"
End If
If AppRedacted.Offset(oset, 0) = "No" Then
.Offset(RowCount + 20 + oset, 1) = "Was application redacted correctly?"
.Offset(RowCount + 20 + oset, 2) = AccountNumber
.Offset(RowCount + 20 + oset, 3) = DateRequested
.Offset(RowCount + 20 + oset, 4) = "No"
.Offset(RowCount + 20 + oset, 5) = "Yes"
End If
If AdditionalAccount.Offset(oset, 0) = "No" Then
.Offset(RowCount + 21 + oset, 1) = "Were additional media/account numbers requested?"
.Offset(RowCount + 21 + oset, 2) = AccountNumber
.Offset(RowCount + 21 + oset, 3) = DateRequested
.Offset(RowCount + 21 + oset, 4) = "No"
.Offset(RowCount + 21 + oset, 5) = "Yes"
End If
If ResultsColumn.Offset(oset, 0) = "No" Then
.Offset(RowCount + 22 + oset, 1) = "Was results column on spreadsheet correct?"
.Offset(RowCount + 22 + oset, 2) = AccountNumber
.Offset(RowCount + 22 + oset, 3) = DateRequested
.Offset(RowCount + 22 + oset, 4) = "No"
.Offset(RowCount + 22 + oset, 5) = "Yes"
End If
If SystemRecord.Offset(oset, 0) = "No" Then
.Offset(RowCount + 23 + oset, 1) = "Was the System of Record documented correctly?"
.Offset(RowCount + 23 + oset, 2) = AccountNumber
.Offset(RowCount + 23 + oset, 3) = DateRequested
.Offset(RowCount + 23 + oset, 4) = "No"
.Offset(RowCount + 23 + oset, 5) = "Yes"
End If
If FolderName.Offset(oset, 0) = "No" Then
.Offset(RowCount + 24 + oset, 1) = "Did the folder name match the spreadsheet?"
.Offset(RowCount + 24 + oset, 2) = AccountNumber
.Offset(RowCount + 24 + oset, 3) = DateRequested
.Offset(RowCount + 24 + oset, 4) = "No"
.Offset(RowCount + 24 + oset, 5) = "Yes"
End If
If SheetStructure.Offset(oset, 0) = "No" Then
.Offset(RowCount + 25 + oset, 1) = "Is spreadsheet structure correct?"
.Offset(RowCount + 25 + oset, 2) = AccountNumber
.Offset(RowCount + 25 + oset, 3) = DateRequested
.Offset(RowCount + 25 + oset, 4) = "No"
.Offset(RowCount + 25 + oset, 5) = "Yes"
End If
If Other.Offset(oset, 0) = "No" Then
.Offset(RowCount + 26 + oset, 1) = "Other (Please provide comment)"
.Offset(RowCount + 26 + oset, 2) = AccountNumber
.Offset(RowCount + 26 + oset, 3) = DateRequested
.Offset(RowCount + 26 + oset, 4) = "No"
.Offset(RowCount + 26 + oset, 5) = "Yes"
End If




'loop for next offset
Next oset
End With


End Sub
 
Upvote 0
DoesVolumeMatch should be declared as Range.
 
Upvote 0
I think it would help if you uploaded all the original code you have that would not run to an online file sharing site (dropbox would be ideal) and then post a link to that uploaded file so that we can see all of what you have instead of a small part of it.
 
Upvote 0
It is a code that I'm working on from work and their computers wont let me access dropbox or google drive. But here is a shortened version of it. The rest of the code is just repeating the two sections I posted in my first post until they each reach 250. The rest of the code is here though. Thanks again for all of the help you all are providing me with.

Code: 
 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")
SubmissionID2 = Range("a5")
BatchName2 = Range("b5")
AccountNumber2 = Range("c5")
DateRequested2 = Range("d5")
DateReviewed2 = Range("f5")
RetrievalAssociate2 = Range("g5")
DoesVolumeMatch2 = Range("j5")
WasCorrectMediaAttached2 = Range("k5")
WasPDFNamedCorrectly2 = Range("l5")
AppRedacted2 = Range("m5")
AdditionalAccount2 = Range("n5")
ResultsColumn2 = Range("o5")
SystemRecord2 = Range("p5")
FolderName2 = Range("q5")
SheetStructure2 = Range("r5")
Other2 = Range("s5")
SubmissionID3 = Range("a6")
BatchName3 = Range("b6")
AccountNumber3 = Range("c6")
DateRequested3 = Range("d6")
DateReviewed3 = Range("f6")
RetrievalAssociate3 = Range("g6")
DoesVolumeMatch3 = Range("j6")
WasCorrectMediaAttached3 = Range("k6")
WasPDFNamedCorrectly3 = Range("l6")
AppRedacted3 = Range("m6")
AdditionalAccount3 = Range("n6")
ResultsColumn3 = Range("o6")
SystemRecord3 = Range("p6")
FolderName3 = Range("q6")
SheetStructure3 = Range("r6")
Other3 = Range("s6")
SubmissionID4 = Range("a7")
BatchName4 = Range("b7")
AccountNumber4 = Range("c7")
DateRequested4 = Range("d7")
DateReviewed4 = Range("f7")
RetrievalAssociate4 = Range("g7")
DoesVolumeMatch4 = Range("j7")
WasCorrectMediaAttached4 = Range("k7")
WasPDFNamedCorrectly4 = Range("l7")
AppRedacted4 = Range("m7")
AdditionalAccount4 = Range("n7")
ResultsColumn4 = Range("o7")
SystemRecord4 = Range("p7")
FolderName4 = Range("q7")
SheetStructure4 = Range("r7")
Other4 = Range("s7")

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 DoesVolumeMatch2 = "No" Then
.Offset(RowCount + 7, 1) = "Does volume match spreadsheet total?"
.Offset(RowCount + 7, 2) = AccountNumber2
.Offset(RowCount + 7, 3) = DateRequested2
.Offset(RowCount + 7, 4) = "No"
.Offset(RowCount + 7, 5) = "Yes"
End If
If WasCorrectMediaAttached2 = "No" Then
.Offset(RowCount + 8, 1) = "Was the correct media attached?"
.Offset(RowCount + 8, 2) = AccountNumber2
.Offset(RowCount + 8, 3) = DateRequested2
.Offset(RowCount + 8, 4) = "No"
.Offset(RowCount + 8, 5) = "Yes"
End If
If WasPDFNamedCorrectly2 = "No" Then
.Offset(RowCount + 9, 1) = "Was PDF named correctly?"
.Offset(RowCount + 9, 2) = AccountNumber2
.Offset(RowCount + 9, 3) = DateRequested2
.Offset(RowCount + 9, 4) = "No"
.Offset(RowCount + 9, 5) = "Yes"
End If
If AppRedacted2 = "No" Then
.Offset(RowCount + 10, 1) = "Was application redacted correctly?"
.Offset(RowCount + 10, 2) = AccountNumber2
.Offset(RowCount + 10, 3) = DateRequested2
.Offset(RowCount + 10, 4) = "No"
.Offset(RowCount + 10, 5) = "Yes"
End If
If AdditionalAccount2 = "No" Then
.Offset(RowCount + 11, 1) = "Were additional media/account numbers requested?"
.Offset(RowCount + 11, 2) = AccountNumber2
.Offset(RowCount + 11, 3) = DateRequested2
.Offset(RowCount + 11, 4) = "No"
.Offset(RowCount + 11, 5) = "Yes"
End If
If ResultsColumn2 = "No" Then
.Offset(RowCount + 12, 1) = "Was results column on spreadsheet correct?"
.Offset(RowCount + 12, 2) = AccountNumber2
.Offset(RowCount + 12, 3) = DateRequested2
.Offset(RowCount + 12, 4) = "No"
.Offset(RowCount + 12, 5) = "Yes"
End If
If SystemRecord2 = "No" Then
.Offset(RowCount + 13, 1) = "Was the System of Record documented correctly?"
.Offset(RowCount + 13, 2) = AccountNumber2
.Offset(RowCount + 13, 3) = DateRequested2
.Offset(RowCount + 13, 4) = "No"
.Offset(RowCount + 13, 5) = "Yes"
End If
If FolderName2 = "No" Then
.Offset(RowCount + 14, 1) = "Did the folder name match the spreadsheet?"
.Offset(RowCount + 14, 2) = AccountNumber2
.Offset(RowCount + 14, 3) = DateRequested2
.Offset(RowCount + 14, 4) = "No"
.Offset(RowCount + 14, 5) = "Yes"
End If
If SheetStructure2 = "No" Then
.Offset(RowCount + 15, 1) = "Is spreadsheet structure correct?"
.Offset(RowCount + 15, 2) = AccountNumber2
.Offset(RowCount + 15, 3) = DateRequested2
.Offset(RowCount + 15, 4) = "No"
.Offset(RowCount + 15, 5) = "Yes"
End If
If Other2 = "No" Then
.Offset(RowCount + 16, 1) = "Other (Please provide comment)"
.Offset(RowCount + 16, 2) = AccountNumber2
.Offset(RowCount + 16, 3) = DateRequested2
.Offset(RowCount + 16, 4) = "No"
.Offset(RowCount + 16, 5) = "Yes"
End If
If DoesVolumeMatch3 = "No" Then
.Offset(RowCount + 17, 1) = "Does volume match spreadsheet total?"
.Offset(RowCount + 17, 2) = AccountNumber3
.Offset(RowCount + 17, 3) = DateRequested3
.Offset(RowCount + 17, 4) = "No"
.Offset(RowCount + 17, 5) = "Yes"
End If
If WasCorrectMediaAttached3 = "No" Then
.Offset(RowCount + 18, 1) = "Was the correct media attached?"
.Offset(RowCount + 18, 2) = AccountNumber3
.Offset(RowCount + 18, 3) = DateRequested3
.Offset(RowCount + 18, 4) = "No"
.Offset(RowCount + 18, 5) = "Yes"
End If
If WasPDFNamedCorrectly3 = "No" Then
.Offset(RowCount + 19, 1) = "Was PDF named correctly?"
.Offset(RowCount + 19, 2) = AccountNumber3
.Offset(RowCount + 19, 3) = DateRequested3
.Offset(RowCount + 19, 4) = "No"
.Offset(RowCount + 19, 5) = "Yes"
End If
If AppRedacted3 = "No" Then
.Offset(RowCount + 20, 1) = "Was application redacted correctly?"
.Offset(RowCount + 20, 2) = AccountNumber3
.Offset(RowCount + 20, 3) = DateRequested3
.Offset(RowCount + 20, 4) = "No"
.Offset(RowCount + 20, 5) = "Yes"
End If
If AdditionalAccount3 = "No" Then
.Offset(RowCount + 21, 1) = "Were additional media/account numbers requested?"
.Offset(RowCount + 21, 2) = AccountNumber3
.Offset(RowCount + 21, 3) = DateRequested3
.Offset(RowCount + 21, 4) = "No"
.Offset(RowCount + 21, 5) = "Yes"
End If
If ResultsColumn3 = "No" Then
.Offset(RowCount + 22, 1) = "Was results column on spreadsheet correct?"
.Offset(RowCount + 22, 2) = AccountNumber3
.Offset(RowCount + 22, 3) = DateRequested3
.Offset(RowCount + 22, 4) = "No"
.Offset(RowCount + 22, 5) = "Yes"
End If
If SystemRecord3 = "No" Then
.Offset(RowCount + 23, 1) = "Was the System of Record documented correctly?"
.Offset(RowCount + 23, 2) = AccountNumber3
.Offset(RowCount + 23, 3) = DateRequested3
.Offset(RowCount + 23, 4) = "No"
.Offset(RowCount + 23, 5) = "Yes"
End If
If FolderName3 = "No" Then
.Offset(RowCount + 24, 1) = "Did the folder name match the spreadsheet?"
.Offset(RowCount + 24, 2) = AccountNumber3
.Offset(RowCount + 24, 3) = DateRequested3
.Offset(RowCount + 24, 4) = "No"
.Offset(RowCount + 24, 5) = "Yes"
End If
If SheetStructure3 = "No" Then
.Offset(RowCount + 25, 1) = "Is spreadsheet structure correct?"
.Offset(RowCount + 25, 2) = AccountNumber3
.Offset(RowCount + 25, 3) = DateRequested3
.Offset(RowCount + 25, 4) = "No"
.Offset(RowCount + 25, 5) = "Yes"
End If
If Other3 = "No" Then
.Offset(RowCount + 26, 1) = "Other (Please provide comment)"
.Offset(RowCount + 26, 2) = AccountNumber3
.Offset(RowCount + 26, 3) = DateRequested3
.Offset(RowCount + 26, 4) = "No"
.Offset(RowCount + 26, 5) = "Yes"
End If
If DoesVolumeMatch4 = "No" Then
.Offset(RowCount + 27, 1) = "Does volume match spreadsheet total?"
.Offset(RowCount + 27, 2) = AccountNumber4
.Offset(RowCount + 27, 3) = DateRequested4
.Offset(RowCount + 27, 4) = "No"
.Offset(RowCount + 27, 5) = "Yes"
End If
If WasCorrectMediaAttached4 = "No" Then
.Offset(RowCount + 28, 1) = "Was the correct media attached?"
.Offset(RowCount + 28, 2) = AccountNumber4
.Offset(RowCount + 28, 3) = DateRequested4
.Offset(RowCount + 28, 4) = "No"
.Offset(RowCount + 28, 5) = "Yes"
End If
If WasPDFNamedCorrectly4 = "No" Then
.Offset(RowCount + 29, 1) = "Was PDF named correctly?"
.Offset(RowCount + 29, 2) = AccountNumber4
.Offset(RowCount + 29, 3) = DateRequested4
.Offset(RowCount + 29, 4) = "No"
.Offset(RowCount + 29, 5) = "Yes"
End If
If AppRedacted4 = "No" Then
.Offset(RowCount + 30, 1) = "Was application redacted correctly?"
.Offset(RowCount + 30, 2) = AccountNumber4
.Offset(RowCount + 30, 3) = DateRequested4
.Offset(RowCount + 30, 4) = "No"
.Offset(RowCount + 30, 5) = "Yes"
End If
If AdditionalAccount4 = "No" Then
.Offset(RowCount + 31, 1) = "Were additional media/account numbers requested?"
.Offset(RowCount + 31, 2) = AccountNumber4
.Offset(RowCount + 31, 3) = DateRequested4
.Offset(RowCount + 31, 4) = "No"
.Offset(RowCount + 31, 5) = "Yes"
End If
If ResultsColumn4 = "No" Then
.Offset(RowCount + 32, 1) = "Was results column on spreadsheet correct?"
.Offset(RowCount + 32, 2) = AccountNumber4
.Offset(RowCount + 32, 3) = DateRequested4
.Offset(RowCount + 32, 4) = "No"
.Offset(RowCount + 32, 5) = "Yes"
End If
If SystemRecord4 = "No" Then
.Offset(RowCount + 33, 1) = "Was the System of Record documented correctly?"
.Offset(RowCount + 33, 2) = AccountNumber4
.Offset(RowCount + 33, 3) = DateRequested4
.Offset(RowCount + 33, 4) = "No"
.Offset(RowCount + 33, 5) = "Yes"
End If
If FolderName4 = "No" Then
.Offset(RowCount + 34, 1) = "Did the folder name match the spreadsheet?"
.Offset(RowCount + 34, 2) = AccountNumber4
.Offset(RowCount + 34, 3) = DateRequested4
.Offset(RowCount + 34, 4) = "No"
.Offset(RowCount + 34, 5) = "Yes"
End If
If SheetStructure4 = "No" Then
.Offset(RowCount + 35, 1) = "Is spreadsheet structure correct?"
.Offset(RowCount + 35, 2) = AccountNumber4
.Offset(RowCount + 35, 3) = DateRequested4
.Offset(RowCount + 35, 4) = "No"
.Offset(RowCount + 35, 5) = "Yes"
End If
If Other4 = "No" Then
.Offset(RowCount + 36, 1) = "Other (Please provide comment)"
.Offset(RowCount + 36, 2) = AccountNumber4
.Offset(RowCount + 36, 3) = DateRequested4
.Offset(RowCount + 36, 4) = "No"
.Offset(RowCount + 36, 5) = "Yes"
End If
 
Upvote 0
nealtd,

Not sure that tells us much more.
I have roughed up a sheet assuming your data is like ...

Excel Workbook
ABCDEFGHIJKLMNOPQRS
2
3Sub IDBatch nameA/C NummberDate RequestedDate ReviewedRet AssocDoes Vol MatchMed Attach'dPDF NamedApp RedactedAdd'l AccountResultsSystem RecFolder NameSheet StructOther
4SB0001Batch 11234501/01/201605/01/2016BillNoNoNoNoNoNoNoNoNoNo
5SB0002Batch 21234602/01/201606/01/2016SueYesYesYesYesYesYesYesYesYesYes
6SB0003Batch 31234703/01/201607/01/2016FredYesNoNoYesNoNoNoNoNoNo
7SB0004Batch 41234804/01/201608/01/2016CharlieNoYesNoNoNoYesNoNoNoNo
8SB0005Batch 51234905/01/201609/01/2016CharlieNoNoNoNoNoNoNoNoNoNo
9SB0006Batch 61235006/01/201610/01/2016SidNoNoNoNoNoNoNoNoNoNo
10SB0007Batch 71235107/01/201611/01/2016SueNoNoNoNoNoNoNoNoNoNo
11SB0008Batch 81235208/01/201612/01/2016FredNoNoYesNoNoNoNoNoNoNo
12SB0009Batch 91235309/01/201613/01/2016BillYesYesYesYesYesYesYesYesYesYes
13SB0010Batch 101235410/01/201614/01/2016SueYesYesYesYesYesYesYesYesYesYes
14SB0011Batch 111235511/01/201615/01/2016FredYesYesYesYesYesYesYesYesYesYes
15SB0012Batch 121235612/01/201616/01/2016CharlieYesYesYesYesYesYesYesYesYesYes
16SB0013Batch 131235713/01/201617/01/2016CharlieYesYesYesYesYesYesYesYesYesYes
17SB0014Batch 141235814/01/201618/01/2016SidNoNoNoNoNoNoNoNoNoNo
18SB0015Batch 151235915/01/201619/01/2016SueNoNoNoNoNoNoNoNoNoNo
Sheet1

I also have, in the same workbook, (I know yours is in another workbook but lets keep it simple for now) a sheet named Account_Details

Set up something similar in a new workbook and see if the below code is giving you anything like the result you are wanting.
Then we can perhaps take it on from there.
Not necessarily the way I would have set about it but have tried to keep the structure similar to your approach so that you can relate to it.
It is potentially creating a lot of blank rows but may be that is what you want.

Code: 
Sub Testing_Neal ()
Application.ScreenUpdating = False
'*****  If you are Option Specific then Dim the variables below as RANGES !!!!


Set ws1 = Sheets("Sheet1")
Set ws2 = Worksheets("Account_Details")


'***********


LastRow = ws1.Cells(Rows.Count, "A").End(xlUp).Row


'starting cell has a row offset of 0  last cell is offset from starting by LastRow -1
'Start a For loop
For oset = 0 To LastRow - 1 '....do the following


Set SubmissionID = ws1.Range("a4").Offset(oset, 0)
Set BatchName = ws1.Range("b4").Offset(oset, 0)
Set AccountNumber = ws1.Range("c4").Offset(oset, 0)
Set DateRequested = ws1.Range("d4").Offset(oset, 0)
Set DateReviewed = ws1.Range("f4").Offset(oset, 0)
Set RetrievalAssociate = ws1.Range("g4").Offset(oset, 0)
Set DoesVolumeMatch = ws1.Range("j4").Offset(oset, 0)
Set WasCorrectMediaAttached = ws1.Range("k4").Offset(oset, 0)
Set WasPDFNamedCorrectly = ws1.Range("l4").Offset(oset, 0)
Set AppRedacted = ws1.Range("m4").Offset(oset, 0)
Set AdditionalAccount = ws1.Range("n4").Offset(oset, 0)
Set ResultsColumn = ws1.Range("o4").Offset(oset, 0)
Set SystemRecord = ws1.Range("p4").Offset(oset, 0)
Set FolderName = ws1.Range("q4").Offset(oset, 0)
Set SheetStructure = ws1.Range("r4").Offset(oset, 0)
Set Other = ws1.Range("s4").Offset(oset, 0)




'*** This Example using Account_Details sheet in same workbook
'Set myData = Workbooks.Open("H:\0 MediaValidationFormTest.xlsm")


'''''' ???   RowCount = Worksheets("Account_Details").Range("a4").CurrentRegion.Rows.Count


RowCount = ws2.Cells(Rows.Count, "C").End(xlUp).Row


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


'loop for next offset


Next oset


ws2.Select


End Sub



If you are needing to declare your variables then all those that I am setting need to be RANGES !!
 
Upvote 0
You must log in or register to reply here.

Similar threads

B
  • Solved
More Efficient Looping
2
Replies
19
Views
512
bhsoundman
B
P
  • Question Question
Hoping to get help with a Macro!
Replies
0
Views
299
PEM000
P
P
  • Solved
VBA to stop writing to specific sheet based on user selection
Replies
1
Views
422
Paul15
P
K
  • Question Question
create fixtures but 2 vs 2
Replies
7
Views
271
kerm007
K
I
  • Question Question
Advice for Could Not Find The Specific Object message
2
Replies
12
Views
237
ipbr21054
I

Forum statistics

Threads
1,226,840
Messages
6,193,283
Members
453,788
Latest member
drcharle

Share this page

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