RunAutoMacros not working

[wafs]

I'm trying to get this macro to run when the workbook is opened and keep running the whole time. I get no error, but the macro is not running in the background. Can anyone see why this is not working?

Sub Input_NA()
With ActiveWorkbook
.RunAutoMacros xlAutoActivate
End With
Dim cell As Object
Dim reportlocation As String
Dim sh As String
Dim strvar As String
Dim p As Integer
Dim m As String

strvar = "N/A"
sh = "1244 HT-DOC 12"
p = p + 1
m = p + 1

For Each cell In Range("L9:L42")
If cell <> ("#N/A") Then 'vlookup is #N/A and is causing an error2042
reportlocation = "R" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "S" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "Y" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "Z" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AF" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AG" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AH" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AI" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AJ" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AK" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AL" + m
Sheets(sh).Range(reportlocation) = strvar
Else
p = p + 1
m = p + 1
End If
p = p + 1
m = p + 1
Next cell
End Sub

 

[xlbo]

why ? you keep asking questions about this and I will keep giving the same answer - look into sheet CHANGE events. There is no reason I can see to have this running all the time as you only need to make your changes when another change happens..

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

It is not normally possible to have a macro running in the background. While code is running you are locked out of the normal UI.

As Geoff says - if you want code to run in response to events you must code event routines.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[wafs]

I changed it to a changesheet event. It still does not work. Could you take a look and see what I must have missed.

Private Sub Worksheet_Change(ByVal cell As Range)

Dim reportlocation As String
Dim sh As String
Dim strvar As String
Dim p As Integer
Dim m As String

strvar = "N/A"
sh = "1244 HT-DOC"
p = 7
n = 0
p = p + 1
m = p + 1

If Intersect(cell, Range("L9").EntireColumn) Is Nothing Then Exit Sub
If cell = 0 Then
reportlocation = "R" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "S" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "Y" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "Z" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AF" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AG" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AH" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AI" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AJ" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AK" + m
Sheets(sh).Range(reportlocation) = strvar
reportlocation = "AL" + m
Sheets(sh).Range(reportlocation) = strvar
Else
End If
p = p + 1
m = p + 1
End Sub

 

[firefytr]

Well, you could probably boil all that code down to ..

Private Sub Worksheet_Change(ByVal cell As Range)
    If Intersect(cell, Range("L:L")) Is Nothing Then Exit Sub
    If cell = 0 Then
        Sheets("1244 HT-DOC").Range("R9,S9,Y9,Z9,AF9,AG9,AH9,AI9,AJ9,AK9,AL9") = "N/A"
    End If
End Sub

But I have no idea if that will help you or not. Why don't you post what it is you want this code to do for you.

(Hiya Tony! )

-----------
Regards,
Zack Barresse

 

[TonyJollans]

Hi wafs,

The code runs and does something, whether it 'works' I cannot say. What it does, I would personally probably do with formulae rather than code.

What are you trying to do? I think we would all need more detail to help much more.


(Hi Zack )

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[wafs]

I have a report that Qualitity people fill out after testing part numbers (bolts). The program looks up the part number and does a vlookup to bring over specifications for the part number. Some part numbers do not have washer and when the specifications for washer come up 0, I want where the test results go to automatically come up with N/A. If the part number has a washer, then the qualitity people have to key in the test results for the washer.

Let me know if this is enough info to help out and I will try the code and let you know if it works.

 

[TonyJollans]

What do you mean, exactly, when you say 'program'?

If the 0 in column L is generated in code, you can fill in all the other cells in the same code.

If the 0 in column L is the result of a VLOOKUP Formula, (re)calculation of the result is not considered a Change and the event won't fire.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[xlbo]

you could take care of this in a formula by using this construct:

=IF(ISNA(Vlookup_Formula)),"N/A",Vlookup_Formula)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[TonyJollans]

I thought of that Geoff, but if this is a cell for user input, the users might find existing contents confusing when they have to fill it in.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[firefytr]

With an exact match, if a result is not found, VLOOKUP will automatically return a *native* #N/A error. So .. why not just have a VLOOKUP formula all on it's own???

-----------
Regards,
Zack Barresse

 

[wafs]

I have vlookup to look up the specifications of the part, in the test area, I just want Not applicable to show up if there are no washers. If I have a vlookup there, it will be erased when the QA people key in test results.

 

[TonyJollans]

Can I just confirm what you have.
[ol][li]User enters a value in cell #1[/li]
[li]There is a VLOOKUP in cell #2, based on the value in cell #1, for number of washers.[/li]
[li]If cell #2 contains 0 you want to prefill "N/A" in some other user input cells.[/li][/ol]

Is that it or is there some code involved?

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.
Excel VBA Training and more Help at VBAExpress[

 

[wafs]

That is correct. If cell#2 contains 0 I want to fill cell#3 with "N/A". There is no code involved with the vlookup, but I'm tring to create an sheet_change code to put N/A when cell#2 is 0.

 

[firefytr]

No need for any VBA here.

We are not dealing with issues of formula distribution/implimentation, we are dealing with your formula and data range. Meaning that if you are returning 0 it is because a match (with whatever you specified for the match case true/false or 1/0) has been found. If you use 0 as your match type ([range_lookup]) then it will return an actual #N/A error if the value is not found.

And as for Tony's point #3 above, if you want some other cell to return the results from the VLOOKUP formula with the criteria that it will return "N/A" when there is nothing found in the VLOOKUP formula, just link the cell to the VLOOKUP cell. If the formula returns an error (#N/A) then that cell will show it as well.

If you are not supposed to be returning a 0 from your formula results, take a hard look at your data range because it's finding *something* there.

HTH

-----------
Regards,
Zack Barresse