Counting & The Use Of "i" In VBA Code
am trying to understand how Excel knows what "i" references when counting something (rows or columns). Example:
For i = 2 To finalRow (in this example, I was declared as an integer)
I have also seen it declared as a variable using "dim" & another macro that didn't declare it. When must it be declared & does it matter?
View Complete Thread with Replies
Related Forum Messages:
VBA Function For "Mean" Using Array As Argument
I am trying to create a function in VBA (possibly an add-in in the future) to
calculate Relative Standard Deviation (RSD). I would like to be able to use
an equation such as: =RSD(values), where the values are cell references (a
variable quantity) chosen by the user.
To do this I've copied scripts for "Mean" and "StdDev", then wrote a small
function for RSD that calls those two. When I try to implement RSD in the
worksheet (or even just Mean), the cell returns an error: #VALUE! I think
the problem has something to do with the Arr() variable used as the argument,
as I've tried some simpler functions with array arguments and can't get them
to work, but using standard non-array arguments works fine. I can write a
subroutine that calls the functions and displays the correct result, but I
would like to be able to use RSD as a typical function in my worksheet.
If I could get Mean to work properly, I should be able to apply that
knowledge to StdDev and RSD. The text for Mean is listed below. I've tried
quite a few modifications to try to make it work, so if someone could get it
to work and reply with the correct function text (or with another function
that performs similarly), I'd appreciate it. Thanks.
Function Mean(Arr() As Single)
Dim Sum As Single
Dim i As Integer
Sum = 0
For i = 1 To UBound(Arr)
Sum = Sum + Arr(i)
Mean = Sum / UBound(Arr)
Simple VBA "View Code"
Basically in my sheet I have a table of various data, where an entrant can appear more than once. e.g.
I want to make it so that if you type in an input cell (say cell G10) an entrant your interest in (i.e. A, B, C or D), then the corresponding entrants in the table will change font color to red and go bold.
I know this must be done by right clicking on the sheet and entering a code in "View code". I can't use conditional formatting because I've already used up all 3 conditions!
Does Anyone Have The VBA Code To "Convert To Number"
Does anyone know the code to perform the function "Convert to number"? I have a problem where I need to change some "numbers" from an output sheet that are actually "text" format to actual numbers. Just changing the format to "Number" doesn't work - it may have to do with the fact that the numbers/text includes commas but I'm not sure why. But I have tried several times to change the format, and even though the format shows as being changed, code still doesn't recognize the characters as numbers. So I want to "convert to number" that excel does. With the numbers output the way they are as text, I get that warning (Green triangle) and I get the option to "Convert to Number". That works, so I tried to record the macro to see what the code would be, but that operation doesn't record.
Transposing Data From "wide" To "long"
Suppose I have numerical data in Excel within the following columns: Column A: ID, Column B: Amount1, Column C: Amount2, Column D: Amount3 (the column headers are in the first row of the file). So in my Excel file, the first couple of rows of data would look like this:
ID Amt1 Amt2 Amt3
40 7.2 4.6 9.7
80 3.4 6.7 1.3
I want to transpose the data so that there is only one amount per row of data (the data should look like the example below):
40 Amt1 7.2
40 Amt2 4.6
40 Amt3 9.7
80 Amt1 3.4
80 Amt1 6.7
80 Amt3 1.3
This is pretty simple to do using proc transpose in SAS, but was wondering if it was possible to do this "wide" to "long" data manipulation in Excel as well (or even Access if easier to do there)?
VBA Macro Event For Questionnaire With "X" & Yes/No
I have created a worksheet to track/calculate compliance with a number of important items at a number of business locations on a number of questions. This workbook will be posted on a central server that will allow 25+ employees to access it from the field and "double-click" to enter an X in either a "Yes" or "No" column for about 100+ locations (the code also will remove the "X" in either column if another "X" is entered into the other column to prevent double entries). The workbook will be quite elaborate and will be locked down except for those cells unprotected for data entry (identifying location, date, etc.).
Below is the code and it works just fine as written with two columns to chose from to "double-click" and enter an "X" (columns C & D for rows 4 thru 15). I want to "freeze frames" locking columns A & B and allow scrolling to the right to enter each New Location and allow the same code to apply to the same rows but column groupings of E & F, G & H, I & J, ...until I have about 100+ sets of two columns identified in the code.
I have tried a variety of changes to the "Union(Range" and "Intersect(Target, Range" last night and this morning to no avail and have gotten nothing but a headache and a desire to drink heavily from the exercise.
Private Sub Worksheet_BeforeDoubleClick( _
ByVal Target As Range, Cancel As Boolean)
Dim rInt As Range
Dim rCell As Range
Set rInt = Intersect(Target, Range("C4:D15"))
If Not rInt Is Nothing Then
For Each rCell In rInt
rCell.Value = "X"
Set rInt = Nothing
Set rCell = Nothing
Cancel = True
Enhancing Conditional Formatting: Write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" Depending On The Value
I would like to write a function that enhances conditional formatting capabilities in this way: suppose that cell(4,5) contains a number that can be 0 to 5
in the adiacent cell(4,6) i want to put a function that:
1) write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" depending on that value
2)Choose color font depending on value
3)Choose color background depending on value
I wrote this piece of
Public Function StatusResponse(AdiacentCell As Range) As String
Dim thisStatus As String
Dim ThisFontColor As Integer
Dim ThisbkColor As Integer
Select Case AdiacentCell.Cells(1, 1)
Case Is = 0
ThisbkColor = 2
ThisFontColor = 1
StatusResponse = "NO DATA"............
Modifying =INDIRECT("'PD "&$AB12&"'!O13")
I have this formula =INDIRECT("'PD "&$AB12&"'!O13") in worksheet (say Sheet1).
This returns value from O13 of worksheet PD 1 (if AB12=1 in worksheet Sheet1. The worksheets will be PD 2, PD 3, and so on for AB12 = 1,2, so on respectively)
Suppose I make some changes on worksheet PD 1 which makes cell O13 to become P15. Now, in worksheet Sheet1, the formula remains still the same =INDIRECT("'PD "&$AB12&"'!O13"). I want this to be automatically changed to =INDIRECT("'PD "&$AB12&"'!P15") or any other formula that gives this result.
COUNTIF (b4:b65000= "Name" Then Countif G4:g6500="BI")
I have a simple database spread sheet and I need to count a column under certain conditions. In one column I have employee names that appear repeatedly, in another I have codes. I want to be able to count how many times the code appears next to the name.
If b4:b65000 = Sam Douglas then I want to count how many times different codes appear in the adjacent cell.
BI = 3
SI = 1
Find All Instances Of "Text" In A Range, And Add Time Attached
I am not even sure if what I need is a possibility with excel. I need to "find" specific information and the amount of time that is attributed to it. All of the information is provided via drop down box (times as well) in daily segments then grouped by two week (bi-monthly) chunks and have the total times for each information type placed elsewhere. There are notes placed in the excel file to the right of the information you will see when first opening the file.
Counting Sequences If "p" Is Present
I have the following two formula's:
=IFERROR(IF(X12="",X13&" "&MATCH(FALSE,INDEX(X13=X13:X1000,0),0)-1,X12&" "&MATCH(FALSE,INDEX(X12=X12:X1000,0),0)-1),"")
The first shows the current sequence of events (WON or LOST) and the second the best sequence achieved.
They work fine but I would like to modify them to only use the data, if "P" is in column D of that row.
Opening Files At "Persnl Sht" Every Time File Is O
Have not been at VBA long so am prety much "junior" I suppose
What I have so far is folder for each Client! - maximum of 4 files in each folder and could all be open at same time
Have "central" VBA well set out control page with Client Files option and command buttons but files open at "last page saved"
Added worksheet select which opens perfect - after the "macro" yes/no open box opens with "last page saved"
Working with "office 2000 SB" and have tried various ways
workbook open / beforeclose / beforesave - matbe not 2000 objects
Just want client to see file opening with his name nicely shown on worksheet
Would really appreciate suggestions to get file open at client worksheet page from start
Variation On An "if" Function & "lookup"
I have created a workbook for keeping records of all maintenance to vehicles in our fleet.
I have a sheet called Vehicle maintenance input:
& then another 10 sheets with the vehicle callsign.
Sheet 1 :- Input all data sheet
Sheet 2 :- Y4
Sheet 3 :- Y7
& so on.
I need a formulae that will copy multiple info to the relevant sheets, I have found a way of copying multiple info, with the formulae below, but I can't get it to work if there is more than 1 sheet.
VBA "ignoring" Or Exit Sub With "Select All"
I am trying to prevent a out of memory error when all cells as selected.
Basically I am lookinf for a way to test if the user selects all the cells in a sheet.
Then if that happen I want to exit the sub.
IF "Select all" then Exit Sub
But I don;t know what syntax to use for the "select all" part.
I know that cells.select does select all but if I try
If ActiveSheet.Cells.Select is True then Exit Sub
I gets into some strange loop and Excel locks up.
Why Doesn't My If Function Print An "X"
My function is Finding the inputted last name on another sheet then looking up the date at the top of the column and comparing it too the current date, However I can't get the current cell to = "X" when they do equal.
Private Sub ReturningRush_Click()
Dim Value7 As String
Dim strCheck As Range
Value7 = InputBox(prompt:="Enter LAST Name")
Set strCheck = .Range("B6:B200").Find(what:=Value7, LookIn:=xlValues, Lookat:=xlWhole, _SearchOrder:=xlByRows,
If Not strCheck Is Nothing Then
Forumla To Determine Outline "parent"
I have created a quick ouline for a chart of accounts. I would like to have a formula that verifies that all the 'children' sum up to the parent at each level of the outline.
I have a column with the integer value of the outline level, 0 being the "root", and each sub-level going up. i.e. 1's would be children of 0s, 3's children of 2's, etc.
0 Entity Value
1 Child1 Value
1 Child2 Value
2 Child1a Value
2 Child2b Value
1 Child3 Value
0 Entity2 Value
How would you write a formula that verifies that a row's value is the sum of the children that are associated with it?
How Do I "SUMIF" Multiple Conditions Are True
I'm trying to sum the values in a column if 2 different conditions (in 2 other columns) are true (so I'm evaluating 3 columns total).
For example, if I had a list of the prices of all the cars available at a dealership (each car listed on a different row), and I wanted to find the total cost of all toyota celicas listed, how would I do this?
Basically, I'm trying to create a formula that says "if the value in the make column = toyota AND the value in the model column = celica, then sum the related values in the price column."
"The Converter Failed To Open The File"
I am using Excel 2002 on an xp system
The database is of information relating to homes for sale in my area.
When I try to open my database I am getting a message in a box that flashes for a second that says:
File conversion in progress
there is a "cancel" option with this message but there is only a half second to click on it which I am unable to do.
Then this message appears:
The converter failed to open the file.
With this message the program opens, but the screen is a dark grey. Very few of the buttons are useable.
These messages only started appearing recently. The file has been in constant use for several months.
The data seems to be intact evidenced by the file size. But the data is not viewable.
VBA Code Error '1004' "Application-defined Or Object -defined Error"
In one worksheet i have created the following code, which adds date to
specific column when data is entered in colum A.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Column = 1 Then
Target.Offset(0, 10).Value = Now()
The problem is that when trying to delete a whole row, error message pops-up.
Run-time error '1004' Application-defined or object -defined error. When click "End" actually everthing is ok, the row has been deleted, but was just wondering what is causing that error.
Items From Collection Using "For-Each-Next" Loop
i have 3 questions related to the case below:
I have the next code and i`m using For Each-Next loop technique.
Somewhere i read that "For Each-Next" is slower than "For-To-Next"
loop. I have to put items from a collection in specific cells location
fill a matrix or fill an array.
1)Is possible to do the same job without use "For Each-Next" loop?
2)How can i extract items from collection like they were a matrix elements?
3)Can i extract, for example, the 4th, 5th or 11th element of one collection if i want?
For Each Item in Collection
How To Select Value Most At Right In An "irregular" Table
I feel quite stupid about this one as it seems easy at first glance, but I just can't figure it out ...
I have a table with 4 columns (A B C D)and need to output in a 5th column (RESULT) the value that is most at the right on each line. The trick is that this value can be in any of the 4 colums, and is not always the biggest (max) value ...
A B C D RESULT 1 2 2 2 1 1 4 3 3 1 2 3 4 4
How To Create A "notes" Drop Down
I have a sheet with 600+ rows(customers) and would like users to enter in new notes and be able to view old notes along with the date entered.
I am trying to create a page that will allow the user to enter in multiple notes in one row via some kind of drop down that will date the notes as they are entered. This way the user can click the drop down and see all the notes entered for that data set ect.
If this is not possible is there another solution? perhaps a referencing function that displays all prior notes and allows you to enter new notes dating each one?
SUMIF Using "Combo Box"
I have the following:
1) A summary worksheet that is using a combo-box to pull Country (US, Canada, Latin America, etc.) and Status (Open, Closed, Pipeline) information.
2) The combo box criteria are pulling Program Type (Program A, Program B, Program C) and Totals by Quarter-Yr based off the Country and Status as per the Combo-box selection.
3) A database of several thousand records hosted in another worksheet but in the same file - worksheet name is "ALL".
4) I have the database worksheet concating the Country, Status, Program Type, and Quarter so the formula used for #2 above looks something like this:
ALL! equals databased worksheet
AO = Concate of "Country&Program&Quarter-Yr&Status"
$B$687 = Combo-box name "Country"
$AA31 = Program A
AT$30 = Qtr-Yr
$B$688 = Combo-box name "Status"
This is working fine if I want to single out each Country but how do I include all...my issue is how I am concating the information in the "ALL" worksheet.
I was thinking a sumif with two conditions might work. Where first if the combo-box pulls up a country then grab that count otherwise grab count for all.
"Max Length:50"? On VLOOKUP
I am using the formula below to perform a VLOOKUP and not return any false hits as I had been getting strange results (nearest match perhaps? Does VLOOKUP default to this?)
For some reason, I am getting a message saying "Max Length:50. Continue?" when I do this, then it returns the value #VALUE. If it finds the match, it returns "2" although the matching value isn't on the sheet!
VBA Code For Copy & Paste
I created this spreadsheet a few montsh ago to help with forecasting for my companies products. The tabs of interest are the HIST vs FORECAST, FORECAST TOOLKIT, and the FORECAST TABLE tab – now the FORECAST TABLE tab is always hidden and if you unhide it then it will rehide itself once one of the macro’s is run. I set it that way to prevent people accidently making adjustments to the forecast data.
The way the sheet is supposed to run is that when you are on the hist vs forecast page then you can click one of the grey buttons on the right which will then graph the numbers for the relevant product on the toolkit page – so far so good, this bit all works fine, there are separate macro’s for this each named after the product they control.
Then once you are on the toolkit you can play about with the numbers until you come up with a 6 month forecast you are happy with which will be on the 6 cell line in the bottom right were it says “ IMPORT”. You can then click on the “IMPORT” button which will then copy and paste it back into the forecast table under the relevant setting – the macro for this is called IMPORT2 and it works off a “product” range and a “ date” range – again this pretty much works as intended.
Dim nDate, nProd
With Sheets("Forecast Toolkit")
nDate = Application.Match(.Range("O31"), Range("Dates")) + Range("Dates")(1).Row - 1
nProd = Application.Match(.Range("I3"), Range("Products")) + Range("Dates")(1).Column...........................
Protect VBA Modules & Code
I would like to protect the VBA modules I have written in a spreadsheet. Is there any way of password protecting these (as there is with Excel workbooks/worksheets) -such that users can not see the VBA code unless they enter a password?
VBA Code To Save As Name & Current Date
I want to schedule a print, for lets say every Sunday at midnight. I have managed to find some what of a solution by using the following vbscript and scheduling a print to default printer. Works perfectly if that is all I wanted.
Dim objExcel, objWorkbook, strFile
' Connect to Excel
Set objExcel = CreateObject("Excel.Application")
strFile = "C: est.xls"
' Open the Excel File for Printing
Set objWorkbook = objExcel.Workbooks.Open _
' Print to Default Printer
Set objSheet = objExcel. ActiveWorkbook.Worksheets(1)
I do not want to print to an actual printer, therefore if I set my default printer as the document writer, the vbscript gets it as far as the "Save As" page. Is there a way I can add to this vbscript so it will save as well? Can I get it to save as "name"&[DATE], thereby having it save as a different name each time?
Can I change this vbscript to print to a named printer rather then a default printer? I am sure a PDF convertor may be of some assistance with this, but I do not have an PDF converter on my computer, and as this will be carried out on a computer that I will not have administration rights, I can not download one.
Step Through & Debug VBA Macro Code
I don't understand the relationships that are happening and what the final value would be if for example the PT was Red. I'm confused and was curious how four lines could all equal different values(highlighted).
UpUserPaint = UCase(RegPaint.Value)
Dim icount As Integer
LenRegPaint = Len(RegPaint.Value)
For icount = 1 To LenRegPaint
ValPAint = ValPAint + Asc(Mid(UpUserPaint, icount, 1))
If PTRed.Value Then
ColorCompare = Abs(Pallet * (ValPAint + PrdtSeed - LenRegPaint))
ColorCompare = Abs( Round(ColorCompare / PrdtSeed - 7227, 0))
ColorCompare = ColorCompare And 6215971
ColorCompare = ColorCompare Xor 6215971.............................
IsError 1004 & Match Function In VBA Code
I am trying and failing to use this
If (IsError(Application.WorksheetFunction.Match(StartAT, GradeArray, 0))) Then
StartAT is a text string that might be blank or a space or other invalid entry, GradeArray is a table which I am using to get a numeric value from a valid text entry. If the entry is invalid then I will use a value from an adjacent cell. The problem is that I get the 1004 error.