Help with a developer tool to be able to change data in certain cells

[suzette0735]

I am working on a spreadsheet that has mark up % in many of the cells in one column. When we want to change all of them to be the same mark up I would like it to be as easy as the click of a button. For example: I want them all to be 25%, or 50% or 10% I want to be able to just select that or input it into a cell and it changes all of the mark up %. However, I do not want to write a formula that would link all of those to one cell where as if I wanted to manually change one it would erase that formula in that particular cell. Does this make sense? Can anyone help with this? Thanks!

 

[dreid1011]

Something like this perhaps:

Private Sub UpdateMarkUp()
Range("A2:A20") = Range("B2")
End Sub

Which can be easily tied to a button or double clicking a specific cell, or entering a specific value, etc.

 

[Joe4]

How about code like this?

Sub MarkUp()

    Dim pct As Byte
    Dim cell As Range
    
    Application.ScreenUpdating = False
    
'   Prompt user for percent of markup
    pct = InputBox("Please enter percent of markup as whole number without percent sign")
    
'   Multiply selected range by markup
    For Each cell In Selection
        If cell <> "" And IsNumeric(cell) Then cell.Value = cell.Value * (1 + (pct / 100))
    Next cell
    
    Application.ScreenUpdating = True
    
End Sub

The way that it works is if you started with something like this:

and wanted to increase each by 25%, what you would do is:
- select the range you want to update (A2:A6 in this example)
- run the "MarkUp" procedure
- enter in your desired percentage (25) as shown below:

and then you would end up with this:

 

[suzette0735]

Something like this perhaps:

Private Sub UpdateMarkUp()
Range("A2:A20") = Range("B2")
End Sub

Which can be easily tied to a button or double clicking a specific cell, or entering a specific value, etc.

Thank you! I am new to this and I do not think I did it right. Please see attachments. There are two different ranges I tried it on and the first went blank and the 2nd nothing happened. Thanks for you help!

 

[suzette0735]

How about code like this?

Sub MarkUp()

    Dim pct As Byte
    Dim cell As Range
   
    Application.ScreenUpdating = False
   
'   Prompt user for percent of markup
    pct = InputBox("Please enter percent of markup as whole number without percent sign")
   
'   Multiply selected range by markup
    For Each cell In Selection
        If cell <> "" And IsNumeric(cell) Then cell.Value = cell.Value * (1 + (pct / 100))
    Next cell
   
    Application.ScreenUpdating = True
   
End Sub

The way that it works is if you started with something like this:
View attachment 116651

and wanted to increase each by 25%, what you would do is:
- select the range you want to update (A2:A6 in this example)
- run the "MarkUp" procedure
- enter in your desired percentage (25) as shown below:

View attachment 116653

and then you would end up with this:
View attachment 116654

This is great ! Thank you! I will see if it works for us

 

[dreid1011]

Thank you! I am new to this and I do not think I did it right. Please see attachments. There are two different ranges I tried it on and the first went blank and the 2nd nothing happened. Thanks for you help!

Okay, you have some errors in the code. Try this:

Sub Button15_Click()
Dim rng As Range
Set rng = Application.Union(Range("N14:N133"), Range("N142:N221"))
rng = Range("N12")
End Sub

 

[suzette0735]

Okay, you have some errors in the code. Try this:

Sub Button15_Click()
Dim rng As Range
Set rng = Application.Union(Range("N14:N133"), Range("N142:N221"))
rng = Range("N12")
End Sub

yay! That worked !! Thanks so much!!

 

[dreid1011]

yay! That worked !! Thanks so much!!

You're welcome, and happy to help.