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:
- Multi Criteria Counting; Count The Number Of Instances That "Project" Appears In Column O AND "TS" In Column N
- Enhancing Conditional Formatting: Write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" Depending On The Value
- Counting Number Of "Friday"s In A Range
- Counting Sequences If "p" Is Present
- Wildcards With Replace ("string","s*r","k") Which Should Give "king")
- Insert Sheet- How Do I Write My Code To Add A Tab Labeled "Reports"
- Receiving Error In Code "unable To Get The Find Property Of The Range Class"
- Code Returns An Error Saying "expected An Array"
- Adding "Data Validation List" By VBA Code
- Share Workbook With "Allow Changes By Multiple Users" Code
- Worksheets, One With "supplier Names", "order Numbers" And "delay In Days" As Columns
- Nested IF Statement (shows Values That If Column A = "build", "nextg" Or "datcen")
- Inscribing Cells: Run A Macro On Enter Keypress, That Would Execute Different Code Depending On That "inscription" That Would Be Invisible To User
- Comment Code Without Having To Introduce " ' " Before Each Line
- The Logic Of OR: IF Statement That Will Evaluate Whether A Variable Is Equal To Any Of The Three Punctuation Marks ".", "?", Or "!"
- Code To Round DOWN To "best Looking" Number
- Use Of "lower" Function In VBA Code.
- Code "debug" Error 13
- Simple VBA "View Code"
- Does Anyone Have The VBA Code To "Convert To Number"
- Rows Of Data Seperated By A "/" Or A "," Or A Blank Space ""
- Formula Not Working: "CCY Mismatch" And If "#N/A Sec" Then "Security Not Found"
- Sumproduct (get The Number Of People "Occupied", "Partly Occupied" And "Available" On A Monday, Tuesday Etc Of The Current Week)
- Lookup Or If Function: Find The Room Nights And Net Room Revenue Values For The Specific Rate Code In The "MATRIX" Workbook
- Macro Won't Execute Code To Put Focus On "print To X Pages Wide By X Pages Tall"
- Index And Vlookup: Match The "Vendor" On The "PO" Worksheet With The "Vendor" On The "Cube File" Worksheet
- Problem Using "Cells" In "Range" "400" Error
- "PC" To Show Up If I Type A Sentence Containing "Called Parent", "Called Dad", Or "Called Mon"
- Macro: Analyzes Data In 365 Sheets ( Named "1" Through "365") And Creates A Table/report With The Results
- Replace All Defined Range Names In The Sheet That Start With "Street" To "Road"
- Date Formula; Gets To 2 Years & 11 Months The Cell Will Go "YELLOW" For 1 Month Only Before It Turns "RED"
- Conditional Formatting: Box That If The Cell Populates "Completed" Or "X Required", Then Highlight As Green
- Macro To Remove Rows That Contains The Phrase "Off Peak" And "Weekend"
- Change The Default Output Values "FALSE" And "TRUE"
- Custom Format Which Displayed "-"(Dashes Without Quotes) Insted Of "0"
- Using ChDrive And ChDir: Check If "PPG" Exists In "C:MEASURE-6000"
- Remove "TRUE" & "FALSE" Words From A Linked Checkbox
- Pivot Table Showed "All" Instead Of "Multiple Items" When Using Datasource From CSV
- Unable To Use "merge And Center","insert" In Unprotected Cells
- Changing Default Settings Of Autofilter...change From "equals" To "contains"
- Resizing Diagrams Without Getting A "compressed" Or "stretched" Result
- If Then In Macro: If S2 Is Empty The It Will Skip Down To "Columns("W:X").Select"
- VBA Code Error '1004' "Application-defined Or Object -defined Error"
- Another Nested IF (empty Then The Target Cell Should Say "No" Otherwise "Yes")
- Conditional Formatting "1"=Black Or "Z"=Red In 1 Condition
- Using A "Find" Based Function Instead Of "Autofilter"
- Document With Two Tabs, One "data" One "offer"
- Replace "." With "" Different Effect When Used On Text And Numbers
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"............
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.
Receiving Error In Code "unable To Get The Find Property Of The Range Class"
I am receiving the error "unable to get the find property of the range class" in my code. I have attached my code and highlighted red where my error is occuring.
On Error Goto ProductionRptEngineListExport_Error
Dim objWrkTmp As Excel.Workbook
Dim ObjWrkshtActive As Excel.Worksheet
Dim objWrkshtTmp As Excel.Worksheet
Dim RngStart As Excel.Range
Dim CurrentRowNum As Long, RowCnt As Long
Dim GroupRowNum As Long, ColCnt As Long
Dim chtChart As Excel. chart
Dim intwrktmp As Integer, intwrktmpTot As Integer, intLastRow As Long
Dim sngwrk As Single
ctlStatus = "Opening Production Report..."
Code Returns An Error Saying "expected An Array"
The following code returns an error saying "expected an array" even though I define the array at the start of the code? Why is it doing this?
arrDays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")
sglArrPos = 0
For sglLoop = 1 To 5
sglArrPos = sglArrPos + 1
Share Workbook With "Allow Changes By Multiple Users" Code
Although i am using VBS, the coding should be similar. I am looking for all Excel files in a folder (WORKING) and determining if they are shared, allowmultipleusers, (WORKING). The problem I am encountering is changing the Excel workbook to a [SHARED] workbook. By that I mean Multiple users can open and make changes. What is the syntax to enable that? As an FYI I can save the [SHARED] workbooks as exclusive (normal), but not the reverse. I have tried Saveas,,,,,,xlshared, but is not working. Here is the VBS code I have (For grins)
Const APPLICATION_DATA = &H1a&
Set objShell = CreateObject("Shell.Application")
'Set objFolder = objShell.Namespace("L:Shared Services Stock Rotation ReviewRotations for Review")
Set objFolder = objShell.Namespace("C:Documents and Settingsmhast14DesktopRotations for Review")
Set objFolderItem = objFolder.Self
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objExcel = CreateObject("Excel.Application") ..............
Worksheets, One With "supplier Names", "order Numbers" And "delay In Days" As Columns
I have two worksheets, one with "supplier names", "order numbers" and "delay in days" as columns. The other one with "supplier names" and "average delays" as columns.
It is the column "average delays" that I have issues with, I need Excel to search the "supplier names" column in the "delays" sheet and identify every specific supplier name, connect the delay in days for that order and calculate the average delay in the right cell of the column "average delays" in the sheet "delay statistics".
I have tried the help files and to search this forum but I have found nothing. I have also with my knowledge tried a few different ways using the IF function but nothing so far.
This is something I started doing but it is of course far away from any truth.
This is the "delays" sheet where I want to find my info. Nothing in the delay column stands for "no delay"...Kinda obvious but you know... So even the "nothing" needs to be included in the calculation..
Inscribing Cells: Run A Macro On Enter Keypress, That Would Execute Different Code Depending On That "inscription" That Would Be Invisible To User
Is there any way to "Inscribe" a cell? I would like to run a macro on Enter keypress, that would execute different code depending on that "inscription" that would be invisible to user. I could use some properties of . Validation property like this:
Private Sub EnterPressed
'following code to ensure proper functioning of Enter in any other Worksheet
If ActiveSheet <> mySheet 'MySheet is global Variable then
'now the real code
If ActiveCell.Validation.InputMessage = "1" Then
The problem is, I use Data Validation and Conditional Formatting, so can't use any of these properties.
Comment Code Without Having To Introduce " ' " Before Each Line
Is there a way to comment code without having to introduce ' before each line? Very much like in C language, we use /* to open comment lines and */ to close comment lines. I know we can use _ [underscore] but I find it not very pratical. Both solutions for comenting in VBA aren't very pratical, specially when copying and pasting coments from a text editor for example.
Code To Round DOWN To "best Looking" Number
This might be a bit hard to explain, but I would like a macro that finds the auto-sum total (currency) of the active worksheet in column H, copies that number, and pastes a rounded down version of that number into B18 of the "totals" worksheet.
Here would be some examples of how I would interpret as "good looking"
$17,130.00 would be rounded to $17,000
$1,245,313.00 would be rounded to $1,200,000.00
$495,000.00 would be rounded to $400,000.00
$12,659.00 would be rounded to $12,000.00
$57,000.00 would be rounded to $50,000.00
The method of coding might be..hmmm...maybe round down to first lowest "whole" number within 10%? I'm not sure how to word it.
Code "debug" Error 13
The two code extracts below are associated with two separate worksheets in the same workbook. They work fine. They simply are used to assign either a PO# or a Temp Unit #, located in a third sheet, in their respective sheets.
The problem is that when I'm in either the Master or the Work Orders sheets where these two codes are used, and I do something simple like drag down some values or paste something in a cell, I get the "Run time error "13" type mismatch error" in the popup debug window for my VBA code. And in each case, it highlights the codeline I've color coded below:
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.
Rows Of Data Seperated By A "/" Or A "," Or A Blank Space ""
I need to prevent users from entering several rows of data seperated by a "/" or a "," or a blank space ""
We use unique identifiers (around 500,000 of them) so I cant really use a drop down box to populate and then make the user select.....
If a user populates a cell with "10005486 / 10045446" or "10005486,10045446" I want to highlight a cell red and then count the instances of red cells on another tab so I can track "errors"
I was intending to use conditional formatting when a "/" or "," or " " is used within a cell. If a user makes this error, I should probably include a msgbox saying why the entry they have made is invalid also....
Lookup Or If Function: Find The Room Nights And Net Room Revenue Values For The Specific Rate Code In The "MATRIX" Workbook
I am having a bit of a problem creating a formula for this report that I have to update every week. I need to be able to find the room nights and net room revenue values for the specific rate code in the "MATRIX" workbook.
On "CURRENT REPORT," I need D4 to pull up the value on "MATRIX" that equals "Room Nights" in column H and "CONABC" in column J. The same thing needs to happen for F4 but with "Net Room Revenue" in column H. The full report has about 500 of these codes in column J, and I need a formula that I can copy easily and will not be affected if codes are added or removed. This is super last minute - I need to finish this report by tomorrow morning, so take a look at the attachments.
Index And Vlookup: Match The "Vendor" On The "PO" Worksheet With The "Vendor" On The "Cube File" Worksheet
I have attached an example workbook to this message, with the sensitive data removed. I am trying to vlookup the "cube" for the product number, into the PO worksheet, IF the vendor numbers match.
On row 2 of the "PO" worksheet, part number AC1000110, should have a cube of 2.5 for vendor # 11170. I'm trying to match the "Vendor" on the "PO" worksheet with the "Vendor" on the "Cube File" worksheet, and then return the corresponding "Cube", (in column F of the "Cube File"), in cell U2 of the "PO" sheet. So, what formula needs to be entered in cell U2?
Problem Using "Cells" In "Range" "400" Error
I am having problems referrring to a range using .Cells
If I try to use the remarked code (red text), it crashes with VBA "400" error for which I cannot find any explanation. It also crashes with same "400" message if I use a cells reference to a numeric column instead of "y").
The code works using the black it loops a column in Sheet("Holidays") and loops a row in Sheet("Schedule"), then color fills 2 ranges in columns in Sheet("Schedule") when the values in 2 cells are equal.
I have attached a scaled down version of my Workbook with this code.
Macro: Analyzes Data In 365 Sheets ( Named "1" Through "365") And Creates A Table/report With The Results
I have a procedure that processes data in a spreadsheet. It analyzes data in 365 sheets ( named "1" through "365") and creates a table/report with the results. When I run this on a scaled down version of my workbook (5 instead of 365 worksheets), the result is almost instantaneous. When this is on my normal workbook, the initial processing is about 1 second per day on Day 1, and the last day it speeds up to almost be instantaneous. It is not a linear relationship between the processing time (still working to get more exact timing information). Also, even if I limit my processing to 5 or 10 sheets, Days 1-10 are always slow and days 350-365 are always fast. The code being run is in the structure as shown below:
numSheets = 365
For counter = 1 To numSheets
x = CalculateSomething(counter)
Private Function calculateSomething(counter As Integer) As Integer
Dim strCounter As String
strCounter = counter
For i = 0 To someNumber
For j = 0 To someDifferentNumber
'Data analyzed on worksheet
The functions obviously aren't copied and pasted/functional, but the relevant efficiency stuff should be there.
Macro To Remove Rows That Contains The Phrase "Off Peak" And "Weekend"
I have a spreadsheet with about 20,000 lines. (The number of rows fluctuate) I need a macro to remove (delete) rows that contains the phrase "Off Peak" and "Weekend"
I was setting it up in a loop but I don't know how to make the loop end after ALL the "Off Peak" and "Weekend" info is removed.
My macro just does a find then delete the row and then loop to the find again. Is there an easier way and can the "Off Peak" and "Weekend" statements be combined into one search
Cells.Find(What:=" Off Peak", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
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.
Another Nested IF (empty Then The Target Cell Should Say "No" Otherwise "Yes")
The formula I have checks for these conditions:
- to see if there is text is cell C221, and if there isn't then the target cell remains blank. (Working).
- if there is text in cell C221, and cell G221 is empty then the target cell should say "No". (Broken! Returns 'FALSE' value instead of "No"...)
- if there is text in cell C221, and cell G221 contains one of the 5 criteria, then the target cell should say "Yes!". (Working).
Here is the formula - how do I fix the broken bit?
=IF((C221=""),"",IF((G221="Condition 1"),"Yes",IF((G221="Condition 2"),"Yes",IF((G221="Condition 3"),"Yes",IF((G221="Condition 4"),"Yes",IF((G221="Condition 5"),"Yes"))))))
Conditional Formatting "1"=Black Or "Z"=Red In 1 Condition
I'm trying to create a formula in 1 conditional format rule. The goal is to check if al cel contains "1" or "Z". If the value is "1" the cell must turn black and if it is "Z" it turns red.
If this is not possible, I'd like to only turn it black.
I am not able to use vba and have only 1 conditional formatting rule left in my excel 2003 sheet.
Using A "Find" Based Function Instead Of "Autofilter"
I work as a data manager at a bear meat factory*. We use a numeric code which refers to the tenderness of each bears flesh and prior to harvest -I often have to sort that data based on the numeric code.
Excel has issues with screening numbers in Autofilter. You can not filter "begins with" or "ends with" if the data is numeric. For Example:
This same problem does not exist in "Find"
Since I seem to be relying on Autofilter for my queries, and it is possible to create a function similar to autofilter using the properly working "find" feature -I was wondering if anyone had already written a function that returns a range and functions similarly to autofilter?
Document With Two Tabs, One "data" One "offer"
i have an excel document with two tabs, one "data" one "offer"
can select parts from a list in "data" (putting a quantity next to the part number) then hit a command button and have the part number, description, quantity and price added to the "offer" tab
(i have a smaller version/sample doc if anyone wants to take a look )
Replace "." With "" Different Effect When Used On Text And Numbers
I make downloads from SAP and they come in the following format:
This is the number format we usually use in Norway.
The problem is that Excel don't understand that for example 1.234,99 is a number because of the ".". They way to fix this manually is to use Replace "." with "". This translates into the following VBA
Selection.Replace What:=".", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
Using this macro will make the following changes:
1.234,99 -> 1234,99
4,2 -> 42
The first is exactly what I wanted (text -> number), but an unwanted side effect is that Excel believes that "." is the decimal separator when faced with a number.
An obvious way to solve this would be to convert everything into text first, but the only way I could think of to do this is as follows:
1) Adding a character before or after every number in a new column (number -> text)
2) Replace "." in the new column
3) Delete character (text -> number)
4) Copy back to the original column