VBA: Column reference variable not working

[ShaanB]

Hi all,
I don't use VBA very often, but I'm hoping someone can help me with this issue I'm having. The code is posted below, my issue is with the sz_col variable in the Columns.Select command
I put the MsgBoxes in to check that the variable was being saved correctly, and it is. If I replace sz_col with 6 then the code works fine. The MsgBox tells me that sz_col = 6. But if I put the sz_col in the command I get an Application-defined or object-defined error. I don't want to use a hard-coded cell reference because I can't control the source document.

I actually have the same problem in another piece of code where it is copying and pasting from 6 different columns on sheet A to sheet B. Columns 1 - 5 copy fine, but in the 6th I get the same error from the variable. Again, if I replace it with the column reference it works fine.


and I've used MsgBoxes to check that the reference is being stored correctly. I put that below as well, just in case....

Thanks for any help you can give!

Sub UPC_Concat()
Dim LastRowU As Long
Dim sty, sz As Range
Dim sty_row, sz_col As String

    LastRowU = Sheets("UPCs").Cells(Rows.Count, 2).End(xlUp).Row
    Set sty = Sheets("UPCs").Range("A1:P" & LastRowU).Find("STYLE #", LookAt:=xlWhole) 'Find style # on the UPC list to set top row
    sty_row = Sheets("UPCs").Range(sty.Address).Row
    Set sz = Sheets("UPCs").Range("A1:P" & LastRowU).Find("SIZE", LookAt:=xlWhole) 'Find size on the UPC list to set where replace needs to occur
    MsgBox sz.Address
    sz_col = Sheets("UPCs").Range(sz.Address).Column
    MsgBox sz_col
      
    Columns(sz_col).Select
    Selection.Replace What:="-", Replacement:=".5", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
 
For i = sty_row + 1 To LastRowU
    Worksheets("UPCs").Range("A" & i).Value = Worksheets("UPCs").Range("B" & i).Value & "-" & _
        Worksheets("UPCs").Range("D" & i).Value & "-" & Worksheets("UPCs").Range("F" & i).Value
Next i
  
    Columns(sz_col).Select
    Selection.Replace What:=".5", Replacement:="-", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

End Sub

    Set st = Sheets("PO").Range("A1:P" & LastRow).Find("STYLE #", LookAt:=xlWhole) 'Find style # on the PO then copy & paste into data sheet
        Set st2 = Sheets("PO").Range(st.Address).Offset(1, 0)
        st_col = Sheets("PO").Range(st.Address).Column
        Worksheets("PO").Range(st2.Address, Cells(LastRow - 1, st_col)).Copy Worksheets("Data").Range("D" & LastRow2 + 1)
    Set sz = Sheets("PO").Range("A1:P" & LastRow).Find("SIZE", LookAt:=xlWhole) 'Find size on the PO then copy & paste into data sheet
        Set sz2 = Sheets("PO").Range(sz.Address).Offset(1, 0)
        sz_col = Sheets("PO").Range(sz.Address).Column
        Worksheets("PO").Range(sz2.Address, Cells(LastRow - 1, sz_col)).Copy Worksheets("Data").Range("A" & LastRow2 + 1)
    Set col = Sheets("PO").Range("A1:P" & LastRow).Find("COLOR", LookAt:=xlWhole) 'Find color on the PO then copy & paste into data sheet
        Set col2 = Sheets("PO").Range(col.Address).Offset(1, 0)
        col_col = Sheets("PO").Range(col.Address).Column
        Worksheets("PO").Range(col2.Address, Cells(LastRow - 1, col_col)).Copy Worksheets("Data").Range("F" & LastRow2 + 1)
    Set d = Sheets("PO").Range("A1:P" & LastRow).Find("DESCRIPTION", LookAt:=xlWhole) 'Find description on the PO then copy & paste into data sheet
        Set d2 = Sheets("PO").Range(d.Address).Offset(1, 0)
        d_col = Sheets("PO").Range(d.Address).Column
        Worksheets("PO").Range(d2.Address, Cells(LastRow - 1, d_col)).Copy Worksheets("Data").Range("E" & LastRow2 + 1)
    Set cst = Sheets("PO").Range("A1:P" & LastRow).Find("ORDER AMOUNT", LookAt:=xlWhole) 'Find cost on the PO then copy & paste into data sheet
        Set cst2 = Sheets("PO").Range(cst.Address).Offset(1, 0)
        cst_col = Sheets("PO").Range(cst.Address).Column
        Worksheets("PO").Range(cst2.Address, Cells(LastRow - 1, cst_col)).Copy Worksheets("Data").Range("G" & LastRow2 + 1)
    Set qty = Sheets("PO").Range("A1:P" & LastRow).Find("TOTAL ORDER QTY", LookAt:=xlWhole) 'Find quantity on the PO then copy & paste into data sheet
        Set qty2 = Sheets("PO").Range(qty.Address).Offset(1, 0)
        qty_cl = Sheets("PO").Range(qty.Address).Column
        Worksheets("PO").Range(qty2.Address, Cells(LastRow - 1, qty_cl)).Copy Worksheets("Data").Range("C" & LastRow2 + 1)

This works:

 Worksheets("PO").Range(qty2.Address, Cells(LastRow - 1, 8)).Copy Worksheets("Data").Range("C" & LastRow2 + 1)

This doesn't:

 Worksheets("PO").Range(qty2.Address, Cells(LastRow - 1, qty_cl)).Copy Worksheets("Data").Range("C" & LastRow2 + 1)

 

[JackDanIce]

I think you're mixing up the sheets and so the numbers do not correspond as you want, are the columns in PO and UPCs in the same order?

Where are LastRow and LastRow2 defined and how does the other bits of code relate to UPC_Concat?

It's not clear what's going on to determine what the problem is - what are you trying to achieve with the macro?

 

[ShaanB]

I think you're mixing up the sheets and so the numbers do not correspond as you want, are the columns in PO and UPCs in the same order?

Where are LastRow and LastRow2 defined and how does the other bits of code relate to UPC_Concat?

It's not clear what's going on to determine what the problem is - what are you trying to achieve with the macro?

Hi Jack,

I don't think the sheets aren't mixed up - as I said, if I change the variable to a fixed column reference it works perfectly, if the sheets were mixed up, that shouldn't work, right?

LastRow & LastRow2 are defined earlier in the code, it's a lot longer and it doesn't relate to UPC_Concat at all. They're two separate piece of code, running on different sheets.

Interestingly, I lost the file after I'd added the problem code to the UPC_Concat procedure and it didn't save. When I re-opened it, I copied the previously "bad" code that had been giving me an error from this message board and put it in, and this time it worked fine....

So the UPC_Concat is actually working as I want it to now....

But the other code still has the same problem.

The PO sheet is a document that comes from a apparel vendor. The goal of the code is to take the poorly formatted (read: nice to print, not nice for working with) data and put it into a clean, concise spreadsheet that we can then save in csv format to import into a point of sale system.
It functions by searching for the header row in the PO document, then copying & pasting the data from below the header into the clean "Data" worksheet. LastRow is the last row of the "PO" sheet (where to copy to) and LastRow2 is the last row of the "Data" sheet (where to start the paste).

I had the first piece of code (with the st variable) and checked and it was working fine, so I copied & pasted the code and changed the variables. The first 5 work fine with the variable, the last one I can't get to work with the qty_cl variable, but if I replace qty_cl with 8 (the actual column number) it works fine.

 

[JackDanIce]

Are the sheets mixed up meaning: are the columns in the same order and the same number of columns in each sheet?

are the columns in PO and UPCs in the same order?

If you don't provide all the information, difficult to guess what the problem is, can't see the spreadsheet set up so it's all guess work I'm afraid.

You're using an excessive and undeeded amount of

.Select

as well as variables in your code, which is likely slowing it down and not clear which parent sheet it's referring to and then makes the code hard to track, follow and decipher.

Or you may have selected sheet PO but trying to select a column you've defined from Data and the parent objects do not match.

Could be wrong but based on what's given so far....

 

[ShaanB]

Hi Jack - the two codes are two completely different workbooks, I simply mentioned the second because on it's face it seems to me to be a similar problem. Otherwise they're completely unrelated, so no the columns are not in the same order.

Unfortunately I can't share the raw spreadsheets as they're proprietary and it would take me quite a while to mock up a fake one (they're really quite bad).

My suspicion here is that it's some kind of bug that I'm unaware of.... As I mentioned, I had the UPC_Concat code and it wasn't working, I kept getting the error, so I came here and posted the code here to see if I was missing something. My computer then crashed and I lost half of that code because the worksheet hadn't saved (I don't like auto-save, it slows everything down). So I came and copied and pasted the entire code from this message board back into the worksheet and it worked fine - no issues. It's exactly the same code that was giving me issues before the crash, but after re-pasting it, it's fine....

 

[RoryA]

You can't use a String variable containing a column number with the Cells property. Use a Variant instead.