Formula based on inputted value (VBA)

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


ABCDEFGHI
1JANFEB
2Week12345678
3
4x1111
5y1
6z1
7a11111
WEEK3COUNT5

<COLGROUP><COL style="WIDTH: 30pt; mso-width-source: userset; mso-width-alt: 1462" width=40><COL style="WIDTH: 48pt" span=9 width=64><TBODY>
</TBODY>

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!
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.

hateme28

Board Regular
Joined
Jul 2, 2014
Messages
161
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
 

JayCheezey

New Member
Joined
Jul 25, 2014
Messages
17
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


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!
 

hateme28

Board Regular
Joined
Jul 2, 2014
Messages
161
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.
 

hateme28

Board Regular
Joined
Jul 2, 2014
Messages
161

ADVERTISEMENT

Also if you wanted to use cells instead of an input box and message box you can. I just didn't know your preference.
 

JayCheezey

New Member
Joined
Jul 25, 2014
Messages
17
Also if you wanted to use cells instead of an input box and message box you can. I just didn't know your preference.

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 & "
 

hateme28

Board Regular
Joined
Jul 2, 2014
Messages
161

ADVERTISEMENT

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

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.
 

JayCheezey

New Member
Joined
Jul 25, 2014
Messages
17
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.


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
 

JayCheezey

New Member
Joined
Jul 25, 2014
Messages
17
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.


A
BCDEFGHI
1JANFEB
2Week12345678
3
4x1111
5y1
6z1
7a11111
WEEK3COUNT5


<tbody>
</tbody>

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,445
Messages
5,636,318
Members
416,912
Latest member
danluk12

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
Top