Formula based on inputted value (VBA)

J

JayCheezey

New Member
Joined
Jul 25, 2014
Messages
17
Hi all,

I have a quick question...

This is just a simplified small data piece of a larger file. I just want to know if this is possible before I implement it throughout the entire file.


My goal is to create a macro that allows me to input what week is desired, then it spits out the number count of Week 1 up until the particular week inputted. For example, if I input Week 3, the macro would spit out 5 because up until week 3, there are 5 cells that have values in them. See example bellow


[TABLE="width: 462"]
<COLGROUP><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 48pt" span=9 width=64><TBODY>[TR]
[TD="width: 40, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent, align: center"]A[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]B[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]C[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]D[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]E[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]F[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]G[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]H[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]I[/TD]
[/TR]
[TR]
[TD="class: xl1150, bgcolor: transparent"]1[/TD]
[TD="class: xl1138, bgcolor: transparent"][/TD]
[TD="class: xl1139, bgcolor: silver, colspan: 5"]JAN[/TD]
[TD="class: xl1139, bgcolor: silver, colspan: 3"]FEB[/TD]
[/TR]
[TR]
[TD="class: xl1150, bgcolor: transparent"]2[/TD]
[TD="class: xl1142, bgcolor: transparent"]Week[/TD]
[TD="class: xl1143, bgcolor: transparent"]1[/TD]
[TD="class: xl1144, bgcolor: transparent"]2[/TD]
[TD="class: xl1144, bgcolor: transparent"]3[/TD]
[TD="class: xl1145, bgcolor: transparent"]4[/TD]
[TD="class: xl1143, bgcolor: transparent"]5[/TD]
[TD="class: xl1144, bgcolor: transparent"]6[/TD]
[TD="class: xl1144, bgcolor: transparent"]7[/TD]
[TD="class: xl1145, bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="class: xl1150, bgcolor: transparent"]3[/TD]
[TD="class: xl1160, bgcolor: #ccccff"] [/TD]
[TD="class: xl1147, bgcolor: #ccccff"] [/TD]
[TD="class: xl1148, bgcolor: #ccccff"] [/TD]
[TD="class: xl1148, bgcolor: #ccccff"] [/TD]
[TD="class: xl1149, bgcolor: #ccccff"] [/TD]
[TD="class: xl1152, bgcolor: #ccccff"] [/TD]
[TD="class: xl1148, bgcolor: #ccccff"] [/TD]
[TD="class: xl1153, bgcolor: #ccccff"] [/TD]
[TD="class: xl1154, bgcolor: #ccccff"] [/TD]
[/TR]
[TR]
[TD="class: xl1150, bgcolor: transparent"]4[/TD]
[TD="class: xl1159, bgcolor: transparent"]x[/TD]
[TD="class: xl1146, bgcolor: transparent"][/TD]
[TD="class: xl1157, bgcolor: #99ccff"]1[/TD]
[TD="class: xl1157, bgcolor: #99ccff"]1[/TD]
[TD="class: xl1157, bgcolor: #99ccff"]1[/TD]
[TD="class: xl1157, bgcolor: #99ccff"]1[/TD]
[TD="class: xl1146, bgcolor: transparent"][/TD]
[TD="class: xl1146, bgcolor: transparent"][/TD]
[TD="class: xl1146, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl1150, bgcolor: transparent"]5[/TD]
[TD="class: xl1159, bgcolor: transparent"]y[/TD]
[TD="class: xl1151, bgcolor: transparent"][/TD]
[TD="class: xl1156, bgcolor: transparent"][/TD]
[TD="class: xl1156, bgcolor: transparent"][/TD]
[TD="class: xl1156, bgcolor: transparent"][/TD]
[TD="class: xl1156, bgcolor: transparent"][/TD]
[TD="class: xl1157, bgcolor: #99ccff"]1[/TD]
[TD="class: xl1151, bgcolor: transparent"][/TD]
[TD="class: xl1151, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl1150, bgcolor: transparent"]6[/TD]
[TD="class: xl1159, bgcolor: transparent"]z[/TD]
[TD="class: xl1151, bgcolor: transparent"][/TD]
[TD="class: xl1151, bgcolor: transparent"][/TD]
[TD="class: xl1157, bgcolor: #99ccff"]1[/TD]
[TD="class: xl1151, bgcolor: transparent"][/TD]
[TD="class: xl1151, bgcolor: transparent"][/TD]
[TD="class: xl1158, bgcolor: transparent"][/TD]
[TD="class: xl1151, bgcolor: transparent"][/TD]
[TD="class: xl1151, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl1150, bgcolor: transparent"]7[/TD]
[TD="class: xl1159, bgcolor: transparent"]a[/TD]
[TD="class: xl1146, bgcolor: transparent"][/TD]
[TD="class: xl1155, bgcolor: #93cddd"]1[/TD]
[TD="class: xl1155, bgcolor: #93cddd"]1[/TD]
[TD="class: xl1157, bgcolor: #99ccff"]1[/TD]
[TD="class: xl1155, bgcolor: #93cddd"]1[/TD]
[TD="class: xl1157, bgcolor: #99ccff"]1[/TD]
[TD="class: xl1146, bgcolor: transparent"] [/TD]
[TD="class: xl1146, bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="class: xl1150, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl1150, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]WEEK[/TD]
[TD="class: xl1161, bgcolor: yellow, align: center"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]COUNT[/TD]
[TD="class: xl1161, bgcolor: yellow, align: center"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</TBODY>[/TABLE]

Ideally, what I would like to accomplish is adding the week number to the count formula. Like if I input 3, the count function would extend the count region to B4:D7 or (B4:B7)+2 columns. I know that this is possible if I do if statements from week 1-52. But this would be inefficient if I incorporate this in the entire file I'm working with.


FYI, in the actual code instead of count, I've created a function called CountColor which counts 1 based on cell color and font color. I'm just wondering if what I'm asking is possible before I continue...


Thanks in advance!
 
Sort by date Sort by votes
There's probably a better way, but I just put this together quick.

Code: 
Sub trythis()
Dim rng As Range, cell, cnt As Integer, week As Long, y
'errorhandle
again:
On Error GoTo test:
week = InputBox("Please enter the week number", "week", , 9500, 5500)
test:
    If week < 1 Or week > 1000 Then
        MsgBox "Please enter a number between 1 and 1000"
        GoTo again:
    End If
y = week
y = y + 1
Set rng = Range(Cells(4, 2), Cells(7, y))
For Each cell In rng
    If cell.Value = 1 Then
        cnt = cnt + 1
    End If
Next
'results
MsgBox "The count is " & cnt, vbOKOnly, "Hello"
End Sub
 
Upvote 0
hateme28 said:
There's probably a better way, but I just put this together quick.

Code: 
Sub trythis()
Dim rng As Range, cell, cnt As Integer, week As Long, y
'errorhandle
again:
On Error GoTo test:
week = InputBox("Please enter the week number", "week", , 9500, 5500)
test:
    If week < 1 Or week > 1000 Then
        MsgBox "Please enter a number between 1 and 1000"
        GoTo again:
    End If
y = week
y = y + 1
Set rng = Range(Cells(4, 2), Cells(7, y))
For Each cell In rng
    If cell.Value = 1 Then
        cnt = cnt + 1
    End If
Next
'results
MsgBox "The count is " & cnt, vbOKOnly, "Hello"
End Sub
Click to expand...


Thanks a bunch! This really helps. Just out of curiosity....

What does:

week = InputBox("Please enter the week number", "week", , 9500, 5500)

the 9500 and 5500 stand for?




Also, where would be the best place for me to go if I wanted to get a better understanding of "Dim..." and setting variables?


You've been a great help! If you're ever in Toronto, beer's on me!
 
Upvote 0
You're very welcome.

The 9500 and 500 are screen position. So it's basically x and y coordinates.

not sure of a good place to look up variables. But to set a variable use

Dim 'variable name, starts with lowercase letter always' as 'variable type'

commonly used is string, integer, range, double (real number), and a few others that've slipped my mind.
 
Upvote 0
Also if you wanted to use cells instead of an input box and message box you can. I just didn't know your preference.
 
Upvote 0
hateme28 said:
Also if you wanted to use cells instead of an input box and message box you can. I just didn't know your preference.
Click to expand...

Again, thanks so much for all your help. I'm very close to a breakthrough but one little thing is stopping me. Here's a little more detailed version of my code.

I've created this function...

Function CountColor(range_data As Range, criteria As Range) As Long
Dim datax As Range
Dim xcolor As Long
Dim xfont As Long

xcolor = criteria.Interior.ColorIndex
xfont = criteria.Font.Color

For Each datax In range_data
If datax.Interior.ColorIndex = xcolor Then
If datax.Font.Color = xfont Then

CountColor = CountColor + 1
End If
End If

Next datax
End Function

-----------------------------------------------------------------------------
I incorporated this code with what you helped me with and got this.

Private Sub CommandButton1_Click()

'Set Dimensions
Dim rng As Range, cell, cnt As Integer, week As Long, y
'errorhandle
again:
On Error GoTo test:
week = InputBox("Please enter the week number", "week", , 9500, 5500)
test:
If week < 1 Or week > 52 Then
MsgBox "Please enter a number between 1 and 52"
GoTo again:
End If

y = week
y = y + 1


Sheets("Conventional").Activate
Set rng = Range(Cells(8, 2), Cells(11, y))

Sheets("Control Sheet").Activate
Sheets("Control Sheet").Range("B13").Formula = "=CountColor(" & rng & ", C2)"

End Sub



I keep getting Error: Type Mismatch when I run the code. Any ideas? I have a feeling it has something to do with " & rng & "
 
Upvote 0
Sheets("Control Sheet").Range("B13").Formula = "=CountColor(" & rng & ", C2)"
Click to expand...

what are you looking to have rng do? Do you need the value? Rng.value. Or address, rng.address. Rng won't do anything on it's own how it's being used in that line.
 
Upvote 0
hateme28 said:
what are you looking to have rng do? Do you need the value? Rng.value. Or address, rng.address. Rng won't do anything on it's own how it's being used in that line.
Click to expand...


OKAY, now I owe you two beers!

The code is working find now.

Here's what I tweaked.

Sheets("Control Sheet").Range("B13").Formula = "=CountColor('" & Sheets("Conventional").Name & "'!" & rng.Address & ", C2)"

You have been such a great help! Thank you so much! I'm going to implement this throughout my entire workbook. If anything happens, I'll let you know!


Thanks again! I owe you one!


Jay
 
Upvote 0
Hey,

So I'm almost done the project, and I was hit with an issue that changes everything. Not to mention the amount of adjustments I'm going to have to manually do, I'm stuck and need your help.

So thus far, we've figured out how to...

1) Count a cells using an inputted value.

[TABLE="class: cms_table, width: 462"]
<tbody>[TR]
[/TR]
[TR]
[TD="width: 64, bgcolor: transparent, align: center"]
A[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]B[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]C[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]D[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]E[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]F[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]G[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]H[/TD]
[TD="width: 64, bgcolor: transparent, align: center"]I[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl1150, bgcolor: transparent"]1[/TD]
[TD="class: cms_table_xl1138, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1139, bgcolor: silver, colspan: 5"]JAN[/TD]
[TD="class: cms_table_xl1139, bgcolor: silver, colspan: 3"]FEB[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl1150, bgcolor: transparent"]2[/TD]
[TD="class: cms_table_xl1142, bgcolor: transparent"]Week[/TD]
[TD="class: cms_table_xl1143, bgcolor: transparent"]1[/TD]
[TD="class: cms_table_xl1144, bgcolor: transparent"]2[/TD]
[TD="class: cms_table_xl1144, bgcolor: transparent"]3[/TD]
[TD="class: cms_table_xl1145, bgcolor: transparent"]4[/TD]
[TD="class: cms_table_xl1143, bgcolor: transparent"]5[/TD]
[TD="class: cms_table_xl1144, bgcolor: transparent"]6[/TD]
[TD="class: cms_table_xl1144, bgcolor: transparent"]7[/TD]
[TD="class: cms_table_xl1145, bgcolor: transparent"]8[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl1150, bgcolor: transparent"]3[/TD]
[TD="class: cms_table_xl1160, bgcolor: #CCCCFF"][/TD]
[TD="class: cms_table_xl1147, bgcolor: #CCCCFF"][/TD]
[TD="class: cms_table_xl1148, bgcolor: #CCCCFF"][/TD]
[TD="class: cms_table_xl1148, bgcolor: #CCCCFF"][/TD]
[TD="class: cms_table_xl1149, bgcolor: #CCCCFF"][/TD]
[TD="class: cms_table_xl1152, bgcolor: #CCCCFF"][/TD]
[TD="class: cms_table_xl1148, bgcolor: #CCCCFF"][/TD]
[TD="class: cms_table_xl1153, bgcolor: #CCCCFF"][/TD]
[TD="class: cms_table_xl1154, bgcolor: #CCCCFF"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl1150, bgcolor: transparent"]4[/TD]
[TD="class: cms_table_xl1159, bgcolor: transparent"]x[/TD]
[TD="class: cms_table_xl1146, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1157, bgcolor: #99CCFF"]1[/TD]
[TD="class: cms_table_xl1157, bgcolor: #99CCFF"]1[/TD]
[TD="class: cms_table_xl1157, bgcolor: #99CCFF"]1[/TD]
[TD="class: cms_table_xl1157, bgcolor: #99CCFF"]1[/TD]
[TD="class: cms_table_xl1146, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1146, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1146, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl1150, bgcolor: transparent"]5[/TD]
[TD="class: cms_table_xl1159, bgcolor: transparent"]y[/TD]
[TD="class: cms_table_xl1151, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1156, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1156, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1156, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1156, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1157, bgcolor: #99CCFF"]1[/TD]
[TD="class: cms_table_xl1151, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1151, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl1150, bgcolor: transparent"]6[/TD]
[TD="class: cms_table_xl1159, bgcolor: transparent"]z[/TD]
[TD="class: cms_table_xl1151, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1151, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1157, bgcolor: #99CCFF"]1[/TD]
[TD="class: cms_table_xl1151, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1151, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1158, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1151, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1151, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl1150, bgcolor: transparent"]7[/TD]
[TD="class: cms_table_xl1159, bgcolor: transparent"]a[/TD]
[TD="class: cms_table_xl1146, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1155, bgcolor: #93CDDD"]1[/TD]
[TD="class: cms_table_xl1155, bgcolor: #93CDDD"]1[/TD]
[TD="class: cms_table_xl1157, bgcolor: #99CCFF"]1[/TD]
[TD="class: cms_table_xl1155, bgcolor: #93CDDD"]1[/TD]
[TD="class: cms_table_xl1157, bgcolor: #99CCFF"]1[/TD]
[TD="class: cms_table_xl1146, bgcolor: transparent"][/TD]
[TD="class: cms_table_xl1146, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl1150, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl1150, bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]WEEK[/TD]
[TD="class: cms_table_xl1161, bgcolor: yellow, align: center"]3[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"]COUNT[/TD]
[TD="class: cms_table_xl1161, bgcolor: yellow, align: center"]5

[/TD]
[/TR]
</tbody>[/TABLE]

So.. given this situation, we developed a way to count all cells from the inputted value. So...my question is...

Is it possible to count by columns instead of individual cells? Like say if we were in week 3, the count would be 2 instead of 5?

Again, thank you so much for all your help. Just got this one last small bit haha.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Locked
  • Solved
Formula to store values for each week maintaining the values of previous weeks?
Replies
3
Views
142
Peter_SSs
Peter_SSs
Excelwhatsthat
  • Solved
Finding a cell value that is a few columns away on the same row, based on most recent value.
Replies
3
Views
86
Fluff
Fluff
G
  • Question Question
Sales - Count Weeks on Offer (Retail)
Replies
3
Views
225
awoohaw
awoohaw
M
  • Question Question
VBA - Find first empty cell in a row
Replies
2
Views
836
Mplz
M
J
  • Question Question
Using Sheets().Select in a Worksheet_Activate() macro
2
Replies
10
Views
583
jdun
J

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