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]

Do you get any value when you move your cursor above columnHeading or Instructions!AAx?

Probably there is no sheet called Instructions. Please check spelling.

 

[tazeo]

Do you get any value when you move your cursor above columnHeading or Instructions!AAx?

Probably there is no sheet called Instructions. Please check spelling.

Not sure what you mean by the first sentence?

Just went through and double checked all the spellings and they are correct. Had me worried for a bit as there are spelling errors all through the data and I though I had been lazy and copied one ?

Paying more attention as I F8 through, at line 29 column L is deleted??

 

[Zot]

If you hover your cursor above the variable like above the word columnHeading in VBA Editor, normally you can see the current columnHeading value pop-up. If error is shown or said no value, then you know that part is problem.

 

[tazeo]

If you hover your cursor above the variable like above the word columnHeading in VBA Editor, normally you can see the current columnHeading value pop-up. If error is shown or said no value, then you know that part is problem.

Hmm never noticed that before (yay for learning)

So columnHeading shows columHeading = ""
&
Instructions!AA10! shows Instructions!AA10! = <Object required>

So I am guessing I can't use a cell reference or I have used the code poorly

 

[Zot]

Hmm never noticed that before (yay for learning)

So columnHeading shows columHeading = ""
&
Instructions!AA10! shows Instructions!AA10! = <Object required>

So I am guessing I can't use a cell reference or I have used the code poorly

This is because sheet Instructions has no reference to a workbook (I should have noticed that earlier ). That is why it is easy to define in the beginning say like:

Set wsInst = ActiveWorkbook.Sheets("Instructions") otherwise you have to write ActiveWorkbook.Sheets("Instructions")! long name multiple times.

If you have more than one workbook opened at running time, you do not have to say activate this and that workbook anymore. You can call it straight form the define parameter.

 

[tazeo]

This is because sheet Instructions has no reference to a workbook (I should have noticed that earlier ). That is why it is easy to define in the beginning say like:

Set wsInst = ActiveWorkbook.Sheets("Instructions") otherwise you have to write ActiveWorkbook.Sheets("Instructions")! long name multiple times.

If you have more than one workbook opened at running time, you do not have to say activate this and that workbook anymore. You can call it straight form the define parameter.

So that line: Set wsInst = ActiveWorkbook.Sheets("Instructions")
Is in the code already at line 24

So is that part right or have I got to change it?

 

[Zot]

So that line: Set wsInst = ActiveWorkbook.Sheets("Instructions")
Is in the code already at line 24

So is that part right or have I got to change it?

I did not remember that I have it stated in my code already. So the statement should be just like this
columnHeading = wsInst.Range("AA9") instead of columnHeading = Instructions!AA9

Change all the have reference to sheet Instructions such as
columnHeading = wsInst.Range("AA10") and so on for the rest

 

[tazeo]

I did not remember that I have it stated in my code already. So the statement should be just like this
columnHeading = wsInst.Range("AA9") instead of columnHeading = Instructions!AA9

Change all the have reference to sheet Instructions such as
columnHeading = wsInst.Range("AA10") and so on for the rest

Ahh ok that makes sense.

So run the macro no error - ?
But it deletes column from the instructions sheet, not the Paste one -

So thats the wrong sheet being active isn't it?

 

[Zot]

Ahh ok that makes sense.

So run the macro no error - ?
But it deletes column from the instructions sheet, not the Paste one -

So thats the wrong sheet being active isn't it?

That is why avoid using ActiveSheet unless really required. You cannot keep track which sheet is active at the moment unless you go through code line by line. When you use statement like
wsInstr.Range("A1") = 5 for example, the sheet never get activate but the value will be written to that sheet regardless which sheet is active

Case Else
                'Delete if the cell doesn't contain "Homer"
                If Not wsPaste.Cells(1, currentColumn) = "Homer" Then
                    ActiveSheet.Columns(currentColumn).Delete
                End If

Which sheet the ActiveSheet here refers to? wsPaste? If that is so, then write as
wsPaste.Columns(currentColumn).Delete

Right now it is deleting the column currentColumn of whatever active sheet at that instant, which obviously the wsInst, thus causing the problem.

 

[tazeo]

That is why avoid using ActiveSheet unless really required. You cannot keep track which sheet is active at the moment unless you go through code line by line. When you use statement like
wsInstr.Range("A1") = 5 for example, the sheet never get activate but the value will be written to that sheet regardless which sheet is active

Case Else
                'Delete if the cell doesn't contain "Homer"
                If Not wsPaste.Cells(1, currentColumn) = "Homer" Then
                    ActiveSheet.Columns(currentColumn).Delete
                End If

Which sheet the ActiveSheet here refers to? wsPaste? If that is so, then write as
wsPaste.Columns(currentColumn).Delete

Right now it is deleting the column currentColumn of whatever active sheet at that instant, which obviously the wsInst, thus causing the problem.

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?