I want to nest a `for` loop to cycle through the Range.Value but ("B4") throws me off and this can't be avoided. I was considering using an array to do this but can't seem to get the format correct. "Q3" is a spreadsheet and "Bac Form" is the attached table imported from a PDF that must be kept. This code works wonderfully - but it is clunky! I have 3 other unique PDFs with which to complete a similar task and rather than just forcing this thing to work would like to use this opportunity to improve my VBA for-looping skills.

 Private Sub CommandButton1_Click()
 
 Dim lr As Long
 Dim FolderPath As String
 
 FolderPath = "C:\Users\Joe.Dimaggio\Desktop\PDFs"
 
 lr = Cells(Rows.Count, 1).End(xlUp).Row
 lc = Cells(1, Columns.Count).End(xlToLeft).Column
 
 For x = 2 To lr
 Sheets("Bac Form").Range("A2").Value = _
 Sheets("Q3").Cells(1, 1).Value & Sheets("Q3").Cells(x, 1).Value
 Sheets("Bac Form").Range("A3").Value = _
 Sheets("Q3").Cells(1, 2).Value & Sheets("Q3").Cells(x, 2).Value & _
 " (Third Bacterial Quarter)"
 Sheets("Bac Form").Range("A4").Value = _
 Sheets("Q3").Cells(1, 3).Value & Sheets("Q3").Cells(x, 3).Value
 Sheets("Bac Form").Range("B4").Value = _
 Sheets("Q3").Cells(1, 4).Value & Sheets("Q3").Cells(x, 4).Value
 Sheets("Bac Form").Range("A5").Value = _
 Sheets("Q3").Cells(1, 5).Value & Sheets("Q3").Cells(x, 5).Value
 Sheets("Bac Form").Range("A6").Value = _
 Sheets("Q3").Cells(1, 6).Value & Sheets("Q3").Cells(x, 6).Value
 Sheets("Bac Form").Range("A7").Value = _
 Sheets("Q3").Cells(1, 7).Value & Sheets("Q3").Cells(x, 7).Value
 Sheets("Bac Form").Range("A8").Value = _
 Sheets("Q3").Cells(1, 8).Value & Sheets("Q3").Cells(x, 8).Value
 Sheets("Bac Form").Range("A9").Value = _
 Sheets("Q3").Cells(1, 9).Value & Sheets("Q3").Cells(x, 9).Value
 Sheets("Bac Form").Range("A10").Value = _
 Sheets("Q3").Cells(1, 10).Value & Sheets("Q3").Cells(x, 10).Value
 Sheets("Bac Form").Range("A11").Value = _
 Sheets("Q3").Cells(1, 11).Value & Sheets("Q3").Cells(x, 11).Value
 Sheets("Bac Form").Range("A13").Value = _
 Sheets("Q3").Cells(1, 12).Value & Sheets("Q3").Cells(x, 12).Value
 Sheets("Bac Form").Range("A14").Value = _
 Sheets("Q3").Cells(1, 13).Value & Sheets("Q3").Cells(x, 13).Value
 Sheets("Bac Form").Range("A15").Value = _
 Sheets("Q3").Cells(1, 14).Value & Sheets("Q3").Cells(x, 14).Value & _
 " colony/100 ml"
 Sheets("Bac Form").Range("A16").Value = _
 Sheets("Q3").Cells(1, 15).Value & Sheets("Q3").Cells(x, 15).Value
 Sheets("Bac Form").Range("A17").Value = _
 Sheets("Q3").Cells(1, 16).Value & Sheets("Q3").Cells(x, 16).Value & _
 " MPN/100 ml"
 Sheets("Bac Form").Range("A18").Value = _
 Sheets("Q3").Cells(1, 17).Value & Sheets("Q3").Cells(x, 17).Value
 
 
 
 Worksheets("Bac Form").ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & "\" & _
 Worksheets("Bac Form").Name & "(Q3)" & (x - 1), openafterpublish:=False
 
 Next x
 
 
 
 End Sub

Here is the PDF form after importing it into Excel:

[![Bac Form][1]][1]


 [1]: https://i.sstatic.net/iOehj.png

 
I would like to see this code input "Q4" column 4 data into "B4" and skip "A12" on the PDF table. I understand this should be done in the case area. 

 
 Private Sub CommandButton1_Click()
 
 
 Dim fileName As String
 
 Dim FOLDER_PATH As String
 
 FOLDER_PATH = GetFolder("C:\Projects")
 
 Dim wsBacForm As Worksheet, wsq4 As Worksheet
 Set wsBacForm = Sheets("Bac Form")
 Set wsq4 = Sheets("q4")
 
 Dim q4BaseRow As Long, q4BaseCol As Long '/ Location of the q4 data table
 q4BaseRow = 1
 q4BaseCol = 1
 
 Dim bacBaseRow As Long, bacBaseCol As Long '/ Location of the bac output table
 bacBaseRow = 2
 bacBaseCol = 1
 
 Dim lastRow As Long, lastCol As Long
 lastRow = wsq4.Cells(Rows.Count, q4BaseCol).End(xlUp).row
 lastCol = wsq4.Cells(q4BaseRow, Columns.Count).End(xlToLeft).Column
 
 Dim bacRow As Long, bacCol As Long, q4Row As Long, q4Col As Long
 Dim row As Long, col As Long, counter As Long
 bacCol = bacBaseCol
 
 Dim bacOutputCell As Range, q4HeaderCell As Range, q4DataCell As Range
 Dim outputString As String
 
 For row = q4BaseRow To lastRow - 1
 bacRow = 2
 q4Row = row + 1
 
 For col = q4BaseCol To lastCol
 q4Col = col
 
 Set bacOutputCell = wsBacForm.Cells(bacRow, bacBaseCol)
 Set q4HeaderCell = wsq4.Cells(q4BaseRow, q4Col)
 Set q4DataCell = wsq4.Cells(q4Row, q4Col)
 
 outputString = q4HeaderCell.Value & q4DataCell.Value
 
 Select Case col '/ used a counter so that it is (absolute position of column) agnostic
 
 Case Is = 2
 outputString = outputString & " (Fourth Bacterial Quarter)"
 
 Case Is = 4
 bacRow = bacRow - 1
 bacBaseCol = bacCol + 1
 
 
 Case Is = 12
 bacOutputCell = wsBacForm.Cells(bacRow + 1, bacBaseCol)
 
 Case Is = 15
 outputString = outputString & " colony/100 ml"
 
 Case Is = 17
 outputString = outputString & " MPN/100 ml"
 
 End Select
 
 bacOutputCell.Value = outputString
 bacRow = bacRow + 1
 
 Next col
 
 fileName = FOLDER_PATH & "\" & wsBacForm.Name & "(" & wsq4.Name & ")" & (row)
 wsBacForm.ExportAsFixedFormat Type:=xlTypePDF, fileName:=fileName, openafterpublish:=False
 Next row
 
 End Sub