How do I shorten up this code sequence

[berenger]

I am new to vba code writing and the code I have is working for me but I would like to know if there is a way to shorten it up but continue to function the same way.

If Sheets("Scan Here").Range("O2") = True Then Range("B2").Value = Sheets("Labels1").Range("L1")
If Sheets("Scan Here").Range("O3") = True Then Range("E2").Value = Sheets("Labels1").Range("M1")
If Sheets("Scan Here").Range("O4") = True Then Range("H2").Value = Sheets("Labels1").Range("N1")
If Sheets("Scan Here").Range("O5") = True Then Range("K2").Value = Sheets("Labels1").Range("O1")
If Sheets("Scan Here").Range("O6") = True Then Range("B6").Value = Sheets("Labels1").Range("L2")
If Sheets("Scan Here").Range("O7") = True Then Range("E6").Value = Sheets("Labels1").Range("M2")
If Sheets("Scan Here").Range("O8") = True Then Range("H6").Value = Sheets("Labels1").Range("N2")
If Sheets("Scan Here").Range("O9") = True Then Range("K6").Value = Sheets("Labels1").Range("O2")
If Sheets("Scan Here").Range("O10") = True Then Range("B10").Value = Sheets("Labels1").Range("L3")
If Sheets("Scan Here").Range("O11") = True Then Range("E10").Value = Sheets("Labels1").Range("M3")
If Sheets("Scan Here").Range("O12") = True Then Range("H10").Value = Sheets("Labels1").Range("N3")
If Sheets("Scan Here").Range("O13") = True Then Range("K10").Value = Sheets("Labels1").Range("O3")
If Sheets("Scan Here").Range("O14") = True Then Range("B14").Value = Sheets("Labels1").Range("L4")
If Sheets("Scan Here").Range("O15") = True Then Range("E14").Value = Sheets("Labels1").Range("M4")
If Sheets("Scan Here").Range("O16") = True Then Range("H14").Value = Sheets("Labels1").Range("N4")
If Sheets("Scan Here").Range("O17") = True Then Range("K14").Value = Sheets("Labels1").Range("O4")
If Sheets("Scan Here").Range("O18") = True Then Range("B18").Value = Sheets("Labels1").Range("L5")
If Sheets("Scan Here").Range("O19") = True Then Range("E18").Value = Sheets("Labels1").Range("M5")
If Sheets("Scan Here").Range("O20") = True Then Range("H18").Value = Sheets("Labels1").Range("N5")
If Sheets("Scan Here").Range("O21") = True Then Range("K18").Value = Sheets("Labels1").Range("O5")
If Sheets("Scan Here").Range("O22") = True Then Range("B22").Value = Sheets("Labels1").Range("L6")
If Sheets("Scan Here").Range("O23") = True Then Range("E22").Value = Sheets("Labels1").Range("M6")
If Sheets("Scan Here").Range("O24") = True Then Range("H22").Value = Sheets("Labels1").Range("N6")
If Sheets("Scan Here").Range("O25") = True Then Range("K22").Value = Sheets("Labels1").Range("O6")
If Sheets("Scan Here").Range("O26") = True Then Range("B26").Value = Sheets("Labels1").Range("L7")
If Sheets("Scan Here").Range("O27") = True Then Range("E26").Value = Sheets("Labels1").Range("M7")
If Sheets("Scan Here").Range("O28") = True Then Range("H26").Value = Sheets("Labels1").Range("N7")
If Sheets("Scan Here").Range("O29") = True Then Range("K26").Value = Sheets("Labels1").Range("O7")
If Sheets("Scan Here").Range("O30") = True Then Range("B30").Value = Sheets("Labels1").Range("L8")
If Sheets("Scan Here").Range("O31") = True Then Range("E30").Value = Sheets("Labels1").Range("M8")
If Sheets("Scan Here").Range("O32") = True Then Range("H30").Value = Sheets("Labels1").Range("N8")
If Sheets("Scan Here").Range("O33") = True Then Range("K30").Value = Sheets("Labels1").Range("O8")

 

[jbeaucaire]

That's long, but it's orderly and easy to read. Each line is a truly unique test and result.

Although there are ways to shorten the numbers of lines in the code, the result would be significantly harder to read and edit/maintain.

If that works, I'd say use it.

 

[berenger]

Thank you I appreciate your comments. I am however going to have to repeat this code to work on an additional 19 sheets so if there was a way to reduce the size any help would be great.

 

[jbeaucaire]

Now, the savings might come from here as you add the code for the next 19 sheets. What would be different in the next set of code?

I'm concerned in your code that you include a sheet reference for the Scan Here tests and the Labels source, but not the Range("B2").Value

 

[berenger]

The Range "B2" refers to the cell on Labels1 sheet the remaining sheets that be referenced will be Labels2 thru 20. and that will be the only change on the formula.

 

[berenger]

I failed to mention that this code will be going from Range O2 thru O640 so that will change as well as the sheet named Label1 thru Label20

 

[pgc01]

Hi berenger

I think this code is equivalent to the one you posted. Please test:

Sub Test()
Dim j As Long
 
For j = 0 To 31
    If Sheets("Scan Here").Range("O2").Offset(j) = True Then _
        Range("B2").Offset((j \ 4) * 4, (j Mod 4) * 3).Value = Sheets("Labels1").Range("L1").Offset(j \ 4, j Mod 4)
Next j
End Sub

 

[berenger]

That works perfectly. Now I have the same situation with a OperationButton2 which is the NO feature so in other words when they select no I only want that same field "B2" etc to populate with no value at all so in other words i want it to be empty. Is there a way to throw that into the mix and have it only erase the data for that row? I have the same formula for that as I did on the one you just help me with but with a few different cells being searched.

Here is the code I am using for NO

If Sheets("Scan Here").Range("O2") = False Then Range("B2").Value = Sheets("Labels1").Range("P1")

 

[Smitty]

Hi berenger

I think this code is equivalent to the one you posted. Please test:

Sub Test()
Dim j As Long
 
For j = 0 To 31
    If Sheets("Scan Here").Range("O2").Offset(j) = True Then _
        Range("B2").Offset((j \ 4) * 4, (j Mod 4) * 3).Value = Sheets("Labels1").Range("L1").Offset(j \ 4, j Mod 4)
Next j
End Sub

If that works it's a beautiful thing. I spend a few minutes looking at the code and ended up where Jerry was, that if it works why screw with it?

But you seem to have found the logic to make it fit, which is truly impressive!

 

[berenger]

yeah that code works perfect for my OptionButton1 which is a True statement now I need to figure out a formula that will work with the OptionButton2 which is False. I am hoping i can use that similar code with some tweaking to it so that it populates the correct cells with null value. Only time will tell.