Macro code assist

rickblunt

Well-known Member
Joined
Feb 18, 2008
Messages
609
Office Version
  1. 2019
Platform
  1. Windows
Greetings, I am using the following macro code to reset a table of entered values to zero. The cells are hyperlinked to autoshapes that change colors depending upon the values in the cells. I used the macro recorder to determine the code. My dilemma is this: I have found that the code for my autoshapes only works or recognizes the zeros that I actually typed in during the macro recording (i.e.C46, E46, and G46), it doesnt recognize the zeros that were entered via the autofill (the dragging the handle technique). If I go back and manually type in a zero in say, E54, that autoshape works properly.

My question becomes this, "What is the difference between the zeros that I manually typed in and the ones that were autofilled in". Perhaps I should do this a completely. Thanks

Code:
Sub RemoveValue()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>
    Range( _<o:p></o:p>
        "C46:C66,E46:E66,G46:G66, " _<o:p></o:p>
        ).Select<o:p></o:p>
    Range("U46").Activate<o:p></o:p>
    Selection.ClearContents<o:p></o:p>
    Range("C46").Select<o:p></o:p>
    ActiveCell.FormulaR1C1 = "0"<o:p></o:p>
    Range("E46").Select<o:p></o:p>
    ActiveCell.FormulaR1C1 = "0"<o:p></o:p>
    Range("G46").Select<o:p></o:p>
    ActiveCell.FormulaR1C1 = "0"<o:p></o:p>
    Range("C46").Select<o:p></o:p>
    Selection.AutoFill Destination:=Range("C46:C66"), Type:=xlFillDefault<o:p></o:p>
    Range("C46:C66").Select<o:p></o:p>
    Range("E46").Select<o:p></o:p>
    Selection.AutoFill Destination:=Range("E46:E66"), Type:=xlFillDefault<o:p></o:p>
    Range("E46:E66").Select<o:p></o:p>
    Range("G46").Select<o:p></o:p>
    Selection.AutoFill Destination:=Range("G46:G66"), Type:=xlFillDefault<o:p></o:p>
    Range("G46:G66").Select<o:p></o:p>
    Range("A1").Select<o:p></o:p>
End Sub
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hello,

not sure, but does this work?

Code:
Sub RemoveValue()
    Range("U46").ClearContents
    Range("C46").Value = 0
    Range("E46").Value = 0
    Range("G46").Value = 0
    Range("C46").Copy Range("C46:C66")
    Range("E46").Copy Range("E46:E66")
    Range("G46").Copy Range("G46:G66")
    Range("A1").Select
End Sub
 
Upvote 0
This should work also:

Code:
Sub RemoveValue()
    Range("U46").ClearContents
    Range("C46:C66,E46:E66,G46:G66").value=0
    Range("A1").Select
End Sub
 
Upvote 0
As for the second part of the question (which I have to admit I didn't fully grasp the first time) I'm wondering if a forced recalculate might help.

drop in

Code:
Calculate

at the end of the code to see.
 
Upvote 0
Thanks onlyadafter and excelR8R, both of the codes worked to replace the values, but the cell colors still did not change. I got around it by hard coding all 200+ text insertions and that did the trick. Sure made the macro awfully long though (as you would expect) it takes a couple of seconds to run. Thanks for the ideas and the help - I did use your code to change the first part of the code so that speeded it up a bit. I appreciate the help. RB
 
Upvote 0
Does this work?

Code:
Sub RemoveValue()
    Application.ScreenUpdating = False
    Range("U46").ClearContents
    For Each CEL In Range("C46:C66,E46:E66,G46:G66").Cells
        CEL.Value = 0
    Next
    Application.ScreenUpdating = True
    Range("A1").Select
End Sub

Actually I have another question for you.

What do you mean by "The cells are hyperlinked to autoshapes that change colors depending upon the values in the cells"

I was trying to reproduce your problem & don't have a clue what this means!
 
Upvote 0
Thanks for your interest and assistance. In a nutshell the application is as follows: We have a cleanroom with about 220 HEPA filtering units, these need to be measured for efficiency on a regular basis. I have taken a map (i.e. overhead view) of the cleanroom and used 220 square autoshapes in roughly the proper scale and placed them on the map to show where they are relative to equipment, these are labeled with the filter numbers. In addition, there is a table of cells hyperlinked to these autoshapes where the tech enters in the testing results for each filter unit. When a tech clicks on the autoshape assigned to that filter unit, the appropriate cell is activated and he simply enters in the observed value. This table is used to run graphs and is conditionally formatted to alert if a filter unit is above or below the proper operating parameters. By having the autoshape change color depending upon the cell value, you get a visual representation of the impact to the equipment in the area of an out of spec filter unit(s).

I will give the new code a spin and see how it works out - thanks again. RB
 
Upvote 0
excelR8R the code worked perfect! thanks. Let me know if you want me to send you a screen shot of the autoshapes or anything like that - not sure if I did a good job of explaining it.
 
Upvote 0
excelR8R the code worked perfect! thanks. Let me know if you want me to send you a screen shot of the autoshapes or anything like that - not sure if I did a good job of explaining it.

So it's obviously to do with writing to the individual cells.

Still not sure how you're hyperlinking an autoshape to a cell. I've tried all sorts to achieve this. Don't think the screenshot would help, but if you could describe how you're setting them up in the first place. What version are you using?

It's not that I need it, it's just irking me there's something you can do with Excel I don't know about!
 
Upvote 0
No problem, I just place an autoshape on the worksheet (or a couple of hundred of them...) and right click on each one - then assign a hyperlink to it. I am using XL2003, but I have used the same technique in the earlier versions.
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top