VBA for If Statement help.

K

kac1125

Board Regular
Joined
Jul 31, 2014
Messages
77
Office Version
  1. 365
Platform
  1. Windows
Hello, looking for some help VBA or otherwise. Total WOS starts A1. B2 is a calculation of =16.5-A2 which gives us 9.5. I need a VBA for as follows if B3 is between 95% and 100 I want the value in B2 to be 4 unless it is already greater than 4 like in the example below. In this case I would want to keep it at 9.5. In B2 for example the % on hand is 75% anything between 75 and 79 I would want to be a value of 6.
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD]TOTAL WOS[/TD]
[TD]ORDER WOS[/TD]
[TD]% ON Hand[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]9.5[/TD]
[TD]98%[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD]4.5[/TD]
[TD]75%[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]13.5[/TD]
[TD]92%[/TD]
[/TR]
</tbody>[/TABLE]

The Value ranges I am looking for are as follows:[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]% On Hand[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] Value [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]95% - 100%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 4.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]93% - 95%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]4.50[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]90% - 92%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 4.75 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]85% - 89%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 5.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]83% - 84%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 5.50 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]80% - 82%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 5.75 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]75% - 79%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 6.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]73% - 74%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 6.50 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]70% - 72%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 6.75 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]65% - 69%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 7.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]63% - 64%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 7.50 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]60% - 62%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 7.75 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]55% - 59%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 8.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]53% - 54%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 8.50 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]50% - 52%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 8.75 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]45% - 49%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 9.00 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]43% - 44%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 9.50 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]40% - 42%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 146"]
<tbody>[TR]
[TD="class: xl66, width: 146"] 9.75 [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]35% - 39%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10.0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]33% - 34%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10.50

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]30% - 32%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]10.75[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]25% - 29%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11.0[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]23% - 24%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11.50

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]20% - 23%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]11.75

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 102"]
<tbody>[TR]
[TD="class: xl65, width: 102"]0% - 19%[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]12.0[/TD]
[/TR]
</tbody>[/TABLE]

SO if the calculation in column B ends up being greater than the values in the list above I would like it to remain as is. If the calculation is lower I would like that number to be the value within the range. Please let me know if this makes sense and or if it is possible.

Thank you for your help!
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
Sort by date Sort by votes
wouldn't changing the value in b2 cancel the calculation of that cell, therefore altering any other cells that calculate off that. thus changing the percentage calc. that pushed for b2 to be changed in the first place? a paradox of sorts
 
Upvote 0
What if the calculation was not part of it? Just changing the number to the value associated with the % on hand?
 
Upvote 0
so the % on hand is independent of the calculation in b2? and what I mean is, its usually not a good idea to update a cell with a "hard" number when that cell was the result of a formula. but what you want to do is most deffinetly do-able.
 
Upvote 0
Correct, the % on hand is independent of the calculation in B2. So maybe the calculation in b2 can be included in a VBA?
 
Upvote 0
see how this works for you. if its what you want, you will need to continue the code for all the possibilities. I don't have time to write each one. but you can see how you need to code it by looking at the previous sections. just copy and paste and change accordingly

Code: 
Private Sub CommandButton1_Click()
'**************************************************************************************************************************
'---------NOTE--->cells(i,4).value is the destination cell(you can change that to where ever you want the result to appear*
'**************************************************************************************************************************
 
'--AQUIRE NUMBER OF NAMES IN COLUMN A "MAIN COLUMN"
Set Data = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
     cntr1 = 0
          For Each r In Data
               cntr1 = cntr1 + 1
                    If r.Offset(1) = vbNullString And r.Offset(2) = vbNullString Then
                    End If
          Next r
'--END SEARCH
'95+
For i = 1 To cntr1
     If Cells(i, 3).Value >= 0.95 And Cells(i, 3).Value < 1 Then
          If Cells(i, 2).Value >= 4 Then
               Cells(i, 4).Value = Cells(i, 2).Value
          Else
               Cells(i, 4).Value = 4
          End If
     End If
Next
'93-95
For i = 1 To cntr1
     If Cells(i, 3).Value >= 0.93 And Cells(i, 3).Value < 0.95 Then
          If Cells(i, 2).Value >= 4 Then
               Cells(i, 4).Value = Cells(i, 2).Value
          Else
               Cells(i, 4).Value = 4.5
          End If
     End If
Next
'90-92
For i = 1 To cntr1
     If Cells(i, 3).Value >= 0.9 And Cells(i, 3).Value < 0.92 Then
          If Cells(i, 2).Value >= 4 Then
               Cells(i, 4).Value = Cells(i, 2).Value
          Else
               Cells(i, 4).Value = 4.75
          End If
     End If
Next
'85-89
For i = 1 To cntr1
     If Cells(i, 3).Value >= 0.85 And Cells(i, 3).Value < 0.89 Then
          If Cells(i, 2).Value >= 4 Then
               Cells(i, 4).Value = Cells(i, 2).Value
          Else
               Cells(i, 4).Value = 5
          End If
     End If
Next
'83-84
For i = 1 To cntr1
     If Cells(i, 3).Value >= 0.83 And Cells(i, 3).Value < 0.84 Then
          If Cells(i, 2).Value >= 4 Then
               Cells(i, 4).Value = Cells(i, 2).Value
          Else
               Cells(i, 4).Value = 5.5
          End If
     End If
Next
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
finding changes and looking back
2
Replies
10
Views
411
felixstraube
F
S
  • Solved
VBA WorksheetFunction StDev with Filter and multiple conditions
Replies
4
Views
1K
Fluff
Fluff
G
  • Question Question
VBA Help - Auto Hide Row
Replies
2
Views
915
Akuini
Akuini
M
  • Solved
VBA Transpose data from one sheet into another
Replies
9
Views
598
madi1004
M
F
  • Solved
A simple project update VBA that is not match the projects
Replies
9
Views
410
NoSparks
N

Forum statistics

Threads
1,223,911
Messages
6,175,324
Members
452,635
Latest member
laura12345

Share this page

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