VBA Workbook Syntax

[hayden]

I'm trying to write a macro to create a transpose formula for me.

The transpose formula references another workbook, and worksheet and I can't seem to get the syntax right. Here is my code so far.

Sub buildformulas()
Dim wbname As String
Dim monthname As String
Dim colletter As String
Dim path1 As String
Dim wb2 As Workbook
Dim wb1 As Workbook
Dim ws2 As Worksheet
'Loop for Formulas
Set wb1 = ActiveWorkbook
path1 = "R:\1200\1255\CapacitySurvey_2020\Acct\"
i = 11
wbname = Sheets("Macroinput").Cells(i, 1).Value
Set wb2 = Workbooks.Open(Filename:=path1 & wbname)

For r = 11 To 57
wb1.Activate
monthname = Sheets("Macroinput").Cells(r, 2).Value
colletter = Sheets("Macroinput").Cells(r, 3).Value
wb2.Activate
Set ws2 = wb2.Worksheets(monthname)
wb1.Activate
Cells(r, 11).Formula = "=Transpose('[" & wb2 & "]" & ws2 & "'!$" & colletter & "$21:$" & colletter & "$100)"
Next
End Sub

The problem line is this one:

Cells(r, 11).Formula = "=Transpose('[" & wb2 & "]" & ws2 & "'!$" & colletter & "$21:$" & colletter & "$100)"

If I just enter it by hand I get this formula:

=TRANSPOSE('[Myron Stratton Spring Run Res YE Oct2016.xlsx]December'!$L$21:$L$100)

What am I missing?

 

[Joe4]

Maybe your variables aren't returning what you think they are.

What happens when you return your built-in formula in a Message Box like this?

MsgBox "=Transpose('[" & wb2 & "]" & ws2 & "'!$" & colletter & "$21:$" & colletter & "$100)"

Does it return a string that looks EXACTLY like the formula at the bottom of your initial post?

 

[hayden]

I get this error for ws2.

 

[Joe4]

Did you copy and paste what I have EXACTLY in the previous cell?
You should not get that message, unless you made a typo or have an error elsewhere.

 

[hayden]

I have now, and I get this error:

 

[Joe4]

Please copy/paste your code here, exactly as you currently have it, using the Code Tags.

 

[hayden]

Joe. I just wrote an entirely new loop to loop through the rows in the other sheet and transpose those via two nested loops.

Thanks for trying to help. I'll mark this as resolved.

Mods' you could delete this entire thing if you'd like.

 

[Joe4]

So you have it working the way you need now?
If so, you can just post your new code, and then mark that post as the solution.

Thanks

 

[hayden]

Sub buildformulas()
Dim wbname As String
Dim monthname As String
Dim colletter As String
Dim path1 As String
Dim wb2 As Workbook
Dim wb1 As Workbook
Dim ws2 As Worksheet
'Loop for Formulas
Set wb1 = ActiveWorkbook
path1 = "R:\1200\1255\CapacitySurvey_2020\Acct\"
i = 11
wbname = Sheets("Macroinput").Cells(i, 1).Value
Set wb2 = Workbooks.Open(Filename:=path1 & wbname)
For r = 6 To 58
wb1.Activate
monthname = Sheets("Macroinput").Cells(r, 2).Value
colletter = Sheets("Macroinput").Cells(r, 3).Value
Application.ScreenUpdating = False
For c = 11 To 67

colref = Cells(5, c).Value
Cells(r, c).Formula = "='[" & wbname & "]" & monthname & "'!$" & colletter & "$" & colref
Next
Next
Application.ScreenUpdating = True
End Sub