Convert script from looking for set text to a cell reference

[tazeo]

Using a script (partial below):
'CHECK WHETHER TO KEEP THE COLUMN
Select Case columnHeading
Case "Number", "Detailed Description", "Type", "Date", "Status"
'Do nothing

How can I change the code to be something like: Case A1,
swapping the text for a cell reference where the text is?

Thanks in advance

 

[Zot]

Ok, Normally I would write/record the macro as if I was stepping through the flow. As this wasn't working, I started tinkering within the flow and missed that, but I am happy I picked what was happening

Works on the right sheet now, but deleting everything.

hovering over wsInst.Range("AA10") it says =<Object variable or With block variable not set>

Reckon this is the last bit and I will save myself a few hours a week punching reports for my team!!!!

<Side Question> Zot wouln't be an homage to Johnny Hart and BC would it?

? No. Actually I've never heard of Johnny Hart. You made me googled for it. I'm on different time zone probably 12 hours ahead.

I have no idea since I don;t see the whole code, but a range need to be Set to define. This error normally comes together and probably it is something else like forget to put End If other than forgetting to put End With.

 

[tazeo]

? No. Actually I've never heard of Johnny Hart. You made me googled for it. I'm on different time zone probably 12 hours ahead.

I have no idea since I don;t see the whole code, but a range need to be Set to define. This error normally comes together and probably it is something else like forget to put End If other than forgetting to put End With.

Sub deleteIrrelevantColumns()
    Dim currentColumn As Long, nColumn As Long
    Dim columnHeading As String
    Dim wsPaste As Worksheet, wsInst As Worksheet
    
    Set wsPaste = ActiveWorkbook.Sheets("Paste")
    Set wsInst = ActiveWorkbook.Sheets("Instructions")
    
    wsPaste.Columns("L").Delete
    
    nColumn = wsPaste.Cells(1, Columns.Count).End(xlToLeft).Column

    For currentColumn = nColumn To 1 Step -1

        columnHeading = ActiveSheet.Cells(1, currentColumn).Value

        'CHECK WHETHER TO KEEP THE COLUMN
        Select Case True
            Case columnHeading = wsInst.Range("AA9"), _
            columnHeading = wsInst.Range("AA10"), _
            columnHeading = wsInst.Range("AA11"), _
            columnHeading = wsInst.Range("AA12"), _
            columnHeading = wsInst.Range("AA13"), _
            columnHeading = wsInst.Range("AA14"), _
            columnHeading = wsInst.Range("AA15"), _
            columnHeading = wsInst.Range("AA16"), _
            columnHeading = wsInst.Range("AA17"), _
            columnHeading = wsInst.Range("AA18"), _
            columnHeading = wsInst.Range("AA19")
                'Do nothing
            Case Else
                'Delete if the cell doesn't contain "Homer"
                If Not wsPaste.Cells(1, currentColumn) = "Homer" Then
                    wsPaste.Columns(currentColumn).Delete
                End If
        End Select
    Next
End Sub

Just done 10 hours of work training for a new process, so very brain dead.
So I add under the existing set statements something like:
Set Range = Application.range("wsInst!AA9:AA19")

or do I add something to the columnHeading statements?

<side> So did you find that Zot is the equivalent to God in the BC Comics?

 

[Zot]

This statement
columnHeading = ActiveSheet.Cells(1, currentColumn).Value

Which sheet the ActiveSheet refers to? Change to the define name wsInst or wsPaste. If neither, then put sheet name
ActiveWorkbook.Sheets("sheetname").Cells(1, currentColumn).Value

Aside: I just read who Johnny Hart was.

 

[tazeo]

This statement
columnHeading = ActiveSheet.Cells(1, currentColumn).Value

Which sheet the ActiveSheet refers to? Change to the define name wsInst or wsPaste. If neither, then put sheet name
ActiveWorkbook.Sheets("sheetname").Cells(1, currentColumn).Value

Aside: I just read who Johnny Hart was.

<insert blood-curdling scream here> ?

So
columnHeading = wsInst.Cells(1, currentColumn).Value
worked. The listed cells now have values (and they are correct).

Run the code and it deletes everything from the Paste Sheet. So it's picking up the right values but isn't recognising them in the columns!!! I know the values/column headings are correct as I have a COUNTIF on the instruction sheet to check that the required columns are present in the pasted data. Got a feeling that the macro is still getting confused about what is it looking for (obviously), but can't see where or how?

Aside: ahh from memory, Zot was the name the cavemen characters gave god as that's the sound of lightning . Can be hard to find the collections here in Australia, loved reading them in the paper when I was a kid, and started to collect them.

 

[Zot]

Still did not get this solved? I don't understand
So it's picking up the right values but isn't recognising them in the columns!!!

So, you are in Australia. I thought in the USA. I'm in Malaysia, browsing the Forum during office hour when has nothing much to do

 

[tazeo]

Still did not get this solved? I don't understand
So it's picking up the right values but isn't recognising them in the columns!!!

So, you are in Australia. I thought in the USA. I'm in Malaysia, browsing the Forum during office hour when has nothing much to do

Thanks
Yeah not solved.

Yes its identifying the right values from wsInst, but not applying to the columns in wsPaste.

Yep Australia, side project which I am working on outside of hours (normally) to make my life easier. Got some time now so on the forum to see if there were any thoughts.

 

[Zot]

Thanks
Yeah not solved.

Yes its identifying the right values from wsInst, but not applying to the columns in wsPaste.

Yep Australia, side project which I am working on outside of hours (normally) to make my life easier. Got some time now so on the forum to see if there were any thoughts.

I could not dig in any further since I have no idea how the worksheet's like. I don't see what can cause the problem

 

[tazeo]

I could not dig in any further since I have no idea how the worksheet's like. I don't see what can cause the problem

All good, thanks for all your help, while we didn't solve it, I got massive amounts of learning. I'd buy you a coffee if I could