Counting & The Use Of "i" In VBA Code

May 27, 2009

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?

ADVERTISEMENT

Multi Criteria Counting; Count The Number Of Instances That "Project" Appears In Column O AND "TS" In Column N

Nov 21, 2007

Im trying to construct a nested Countif statement. I need to count the number of instances that "Project" appears in Column O AND "TS" in Column N. The range is in another in Sheet2. and the summary in Sheet 1 where I want to have the Countif(AND...??? statement Example Counif(Sheet 1 Column 0 contains "Project" AND if Column N Contains "TS"

View 2 Replies View Related

Enhancing Conditional Formatting: Write "NO DATA", "HIGH", "GOOD", "MODERATE", "POOR", "BAD" Depending On The Value

Jul 6, 2006

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

View 2 Replies View Related

Counting Number Of "Friday"s In A Range

Feb 8, 2010

I was wanting to count the number of Fridays that appear in the range of 1 Jan 2010 - 31 March 2010. I will be changing the searchable day and range in the future so I need it automated. I expect the output to be a number, example. 13.

View 5 Replies View Related

Counting Sequences If "p" Is Present

May 28, 2009

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),"")

=MAX(FREQUENCY(IF(INDEX(X:X,12):INDEX(X:X,1000)="WON",ROW(INDEX(X:X,12):INDEX(X:X,1000))),IF(INDEX(X:X,12):INDEX(X:X,1000)="LOST",ROW(INDEX(X:X,12):INDEX(X:X,100)))))
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.

View 9 Replies View Related

Wildcards With Replace ("string","s*r","k") Which Should Give "king")

Mar 22, 2009

I can't seem to find out how to use wildcards like "?" and "*" in the VBA- Replace("string","s*r","k") which should give "king"

View 3 Replies View Related

Nested IF Statement (shows Values That If Column A = "build", "nextg" Or "datcen")

Feb 10, 2009

In column A, I have the following values:
build
chil rat
datcen
mang
nextg
ost rat
omp
sco rat

In column B, I need the formula so it shows values that if column A = "build", "nextg" or "datcen", then the value in column B is "nextg" and if column A = "chil rat", "ost rat", "sco rat", then the value in column B is "mig". For everything else, it should be blank.

View 5 Replies View Related

Worksheets, One With "supplier Names", "order Numbers" And "delay In Days" As Columns

Jul 19, 2007

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.

=IF(;Delays!B1:B200="Greber")..............

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..

View 9 Replies View Related

The Logic Of OR: IF Statement That Will Evaluate Whether A Variable Is Equal To Any Of The Three Punctuation Marks ".", "?", Or "!"

Apr 16, 2009

tell me which operator works in VBA the way the OR operator works in functions? I want to write an IF statement that will evaluate whether a variable is equal to any of the three punctuation marks ".", "?", or "!". But I'm not sure how to code it. I attempted the following:

View 2 Replies View Related

Insert Sheet- How Do I Write My Code To Add A Tab Labeled "Reports"

Mar 1, 2009

How do I write my code to add a tab labeled "Reports".

This is the code the Macro Recorder gave me, but it does not work.

Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "Reports"
Sheets("SetUp").Select

View 9 Replies View Related

Receiving Error In Code "unable To Get The Find Property Of The Range Class"

Apr 26, 2006

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..."
DoEvents........................................

View 2 Replies View Related

Code Returns An Error Saying "expected An Array"

Jan 9, 2007

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?

Sub copydays()

arrDays = Array("Monday", "Tuesday", "Wednesday", "Thursday", "Friday")

sglArrPos = 0

For sglLoop = 1 To 5
Sheets(arrDays(0)).Select
Cells.Select
Selection.Copy
sglArrPos = sglArrPos + 1
Sheets(arrDays(sglArrPos)).Select
Cells.Select
ActiveSheet.Paste
Next sglLoop

End Sub

View 9 Replies View Related

Adding "Data Validation List" By VBA Code

Jun 27, 2006

I want to add "Data Validation List" by VBA code.

If in column "A" I have 100 rows of data, I would like that in column be will be 100 "combos" with reference to certaing range.

View 9 Replies View Related

Share Workbook With "Allow Changes By Multiple Users" Code

Aug 8, 2007

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") ..............

View 3 Replies View Related

Sales Growth Per Month Formula...</title><script Type="text/javascript" Src="clientscript/vbulletin_post_loader.js?v=384"></script><style Type="text/css" Id="vbulletin_showthread_css">

Jul 10, 2009

In my spreadsheet below I want to be able to enter a sales number for January, the value of cell F2.

I want cells F3 thru F12 to automatically calculate according to the "Growth Per Month" value in cell H1.

Example: If January sales are 20,000, then February should calculate to 21,000 (january * 105%).

Excel Jeanie HTMLSheet1

E F G H 1 2010 Per Month Sales XXX 5% 2 XXX XXX 3 February $ 10,000 4 March $ 5,000 XXX 5 April $ 2,500 6 May $ 1,250 7 June $ 625 8 July $ 313 9 XXX $ 156 10 September $ 78 XXX 11 October XXX 12 November $ 20 XXX 13 December $ 10 14 $ 39,990
Spreadsheet Formulas Cell Formula F3 =F2*10*H1 F4 =F3*10*H1 F5 =F4*10*H1 F6 =F5*10*H1 F7 =F6*10*H1 F8 =F7*10*H1 F9 =F8*10*H1 F10 =F9*10*H1 F11 =F10*10*H1 F12 =F11*10*H1 F13 =F12*10*H1 F14 =SUM(F2:F13)

View 9 Replies View Related

Inscribing Cells: Run A Macro On Enter Keypress, That Would Execute Different Code Depending On That "inscription" That Would Be Invisible To User

Feb 20, 2007

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
ActiveCell.Offset(1,0).Select
exit Sub
End If
'now the real code
If ActiveCell.Validation.InputMessage = "1" Then
ActiveCell.Offset(0,1).Select
Else
'something else
End If
End Sub

The problem is, I use Data Validation and Conditional Formatting, so can't use any of these properties.

View 8 Replies View Related

Comment Code Without Having To Introduce " ' " Before Each Line

Sep 26, 2008

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.

View 3 Replies View Related

Auto Deletion Of Contents</title> <script Type="text/javascript" Src="clientscript/vbulletin_post_loader.js?v=384"></script><style Type="text/css" Id="vbulletin_showthread_css"> <!-

Dec 20, 2008

As shown in the below image. I have some data from A13 to I 13. Currently if I put the cursor on A13 and press DELETE button then all the formatting from A13 to I13 goes off. But the data still remain there.

Is it possible then when I press DELETE the content of A13 then all the data from B13 to I13 should also get deleted ?

Excel Jeanie HTMLSheet2 *ABCDEFGHI13Key FieldEmp 11 2 4 8 5 3 7 Excel tables to the web >> Excel Jeanie HTML 4

View 9 Replies View Related

Formula Not Working: "CCY Mismatch" And If "#N/A Sec" Then "Security Not Found"

Apr 29, 2009

i just tried the below formulae but its not working. if Currency in col A and Currency col B is same it should be "Matched" if not same the "CCY Mismatch" and if "#N/A Sec" then "Security Not Found"

View 3 Replies View Related

Rows Of Data Seperated By A "/" Or A "," Or A Blank Space ""

Feb 24, 2009

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....

View 9 Replies View Related

Sumproduct (get The Number Of People "Occupied", "Partly Occupied" And "Available" On A Monday, Tuesday Etc Of The Current Week)

Oct 22, 2009

I'm having issues with sumproduct. I can't seem to get the right info that I need. Attached is the file I'm working on. Problem: I need to get the number of people "Occupied", "Partly Occupied" and "Available" on a Monday, Tuesday etc of the current week. "Occupied" means an employee has more than 2 tasks (based on New and Active-To-Date status). "Partly Occupied" means an employee is working on 2 tasks.
"Available" means an employee has NO task at all.

View 2 Replies View Related

Code "debug" Error 13

May 29, 2009

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:

View 2 Replies View Related

Use Of "lower" Function In VBA Code.

Feb 1, 2010

I am trying to use the lower function in VBA. I have tried using both codes below, but neither are working.

View 4 Replies View Related

Simple VBA "View Code"

Oct 31, 2007

Basically in my sheet I have a table of various data, where an entrant can appear more than once. e.g.

ACBDC
AABDC
DBCCD
BCDAB

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!

View 9 Replies View Related

Code To Round DOWN To "best Looking" Number

Oct 16, 2009

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.

View 9 Replies View Related

Does Anyone Have The VBA Code To "Convert To Number"

Dec 17, 2009

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.

View 9 Replies View Related

Index And Vlookup: Match The "Vendor" On The "PO" Worksheet With The "Vendor" On The "Cube File" Worksheet

Nov 20, 2009

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.

Example:
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?

View 2 Replies View Related

Problem Using "Cells" In "Range" "400" Error

Feb 14, 2009

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.

View 2 Replies View Related

Lookup Or If Function: Find The Room Nights And Net Room Revenue Values For The Specific Rate Code In The "MATRIX" Workbook

Apr 7, 2009

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.

View 2 Replies View Related

Macro Won't Execute Code To Put Focus On "print To X Pages Wide By X Pages Tall"

Feb 20, 2009

I'm trying to execute a macro and it won't put the focus (radio button) to select x pages wide by x pages tall in the Page Setup/Page/Scaling Area. I looked at the macro and can't find a setting in the code but yet the focus won't change. What can I do about this? In other words the radio button stays selected as "adjust to "" % of normal size. Here's the code...

View 2 Replies View Related

"PC" To Show Up If I Type A Sentence Containing "Called Parent", "Called Dad", Or "Called Mon"

Dec 2, 2009

I need "PC" to show up if I type a sentence containing "Called Parent", "Called Dad", or "Called Mon". Here is what I'm trying.

View 2 Replies View Related

Macro: Analyzes Data In 365 Sheets ( Named "1" Through "365") And Creates A Table/report With The Results

Mar 13, 2007

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)
Next

Private Function calculateSomething(counter As Integer) As Integer

Dim strCounter As String
strCounter = counter

With worksheets(strCounter)
For i = 0 To someNumber
For j = 0 To someDifferentNumber
'Data analyzed on worksheet
Next
Next
End With
End Function

The functions obviously aren't copied and pasted/functional, but the relevant efficiency stuff should be there.

View 5 Replies View Related

Date Formula; Gets To 2 Years & 11 Months The Cell Will Go "YELLOW" For 1 Month Only Before It Turns "RED"

Jan 24, 2009

I have a file that uses dates, when a cell is in date the cell is "GREEN" but when the cell is out of date after 3 years it turns "RED". I want to add another formular so that when it gets to 2 years & 11 months the cell will go "YELLOW" for 1 month only before it turns "RED".

View 3 Replies View Related

Conditional Formatting: Box That If The Cell Populates "Completed" Or "X Required", Then Highlight As Green

May 12, 2009

I have the folowing criteria :

1) If “Completed" , then highlight as green
2) If “Pending” , then highlight as red
3) If “In Progress” , then highlight as blue
4) If “X Required” , then highlight as green

I need to include in the third conditional formatting box that if the cell populates "Completed" or "X Required", then highlight as green. Is there a formula I can use in the conditional formatting box ? I am using Excel 2000.

View 2 Replies View Related

Replace All Defined Range Names In The Sheet That Start With "Street" To "Road"

Jun 14, 2008

I want to replace all defined range names in the sheet that start with "Street" to "Road"

For example I have 50 defined names in the sheet as such "Street-01", "Street-02"... all through "Street-50"

I want to change them all in vba to Road-01", "Road-02" etc.

View 12 Replies View Related

Change The Default Output Values "FALSE" And "TRUE"

Nov 4, 2008

how to change the default output values "FALSE" and "TRUE" for the AND() function ? Let say I want to change them to "FAIL" "PASS".

View 2 Replies View Related

Custom Format Which Displayed "-"(Dashes Without Quotes) Insted Of "0"

Aug 30, 2009

i have a table in which some value is Equal to 0. Now i want a custom format which Displayed "-"(Dashes without quotes) insted of "0" (without quotes)

View 4 Replies View Related

Remove "TRUE" & "FALSE" Words From A Linked Checkbox

Nov 15, 2008

On the sheets where 'present' needs to be checked, after linking the checkbox to the cell it now says "TRUE" or "FALSE". How do I get rid of that? There is a formula that this affects also (just FYI).

View 3 Replies View Related

Pivot Table Showed "All" Instead Of "Multiple Items" When Using Datasource From CSV

Aug 26, 2009

I use the following code to get pivot table data source throught ODBC connect to CSV. However, when I chose hide item in "Page" fields, it showed "all" instead of "multiple items".

View 3 Replies View Related

Using ChDrive And ChDir: Check If "PPG" Exists In "C:MEASURE-6000"

Jan 14, 2010

I can not figure out why the ChDir command doesn't change to "C:" as programmed, instead it defaults to (I'm guessing last active folder) on my "D" drive (Which is "D:Test")? My goal is to check if "PPG" exists in "C:MEASURE-6000", if not use "C:" as a default.

View 2 Replies View Related

Macro To Remove Rows That Contains The Phrase "Off Peak" And "Weekend"

Feb 27, 2009

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

Do While
Cells.Find(What:=" Off Peak", After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Select
Selection.EntireRow.Delete
Loop

View 9 Replies View Related

ADVERTISEMENT