Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    Excel


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

Sponsored Links:

Related Forum Messages:
Multi Criteria Counting; Count The Number Of Instances That "Project" Appears In Column O AND "TS" In Column N
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 Replies!   View Related
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"............

View Replies!   View Related
Counting Number Of "Friday"s In A Range
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 Replies!   View Related
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.

View Replies!   View Related
Wildcards With Replace ("string","s*r","k") Which Should Give "king")
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 Replies!   View Related
Insert Sheet- How Do I Write My Code To Add A Tab Labeled "Reports"
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").Name = "Reports"

View Replies!   View Related
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..."

View Replies!   View Related
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?

Sub copydays()

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

sglArrPos = 0

For sglLoop = 1 To 5
sglArrPos = sglArrPos + 1
Next sglLoop

End Sub

View Replies!   View Related
Adding "Data Validation List" By VBA Code
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 Replies!   View Related
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)

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 Replies!   View Related
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..

View Replies!   View Related
Nested IF Statement (shows Values That If Column A = "build", "nextg" Or "datcen")
In column A, I have the following values:
chil rat
ost rat
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 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
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
exit Sub
End If
'now the real code
If ActiveCell.Validation.InputMessage = "1" Then
'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 Replies!   View Related
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.

View 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 "!"
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 Replies!   View Related
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.

View Replies!   View Related
Use Of "lower" Function In VBA Code.
I am trying to use the lower function in VBA. I have tried using both codes below, but neither are working.

View Replies!   View Related
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:

View Replies!   View Related
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!

View Replies!   View Related
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.

View 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">
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 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"> <!-
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 Replies!   View Related
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....

View Replies!   View Related
Formula Not Working: "CCY Mismatch" And If "#N/A Sec" Then "Security Not Found"
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 Replies!   View Related
Sumproduct (get The Number Of People "Occupied", "Partly Occupied" And "Available" On A Monday, Tuesday Etc Of The Current Week)
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 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
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 Replies!   View Related
Macro Won't Execute Code To Put Focus On "print To X Pages Wide By X Pages Tall"
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 Replies!   View Related
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?

View Replies!   View Related
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.

View Replies!   View Related
"PC" To Show Up If I Type A Sentence Containing "Called Parent", "Called Dad", Or "Called Mon"
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 Replies!   View Related
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

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

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

View Replies!   View Related
Replace All Defined Range Names In The Sheet That Start With "Street" To "Road"
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 Replies!   View Related
Date Formula; Gets To 2 Years & 11 Months The Cell Will Go "YELLOW" For 1 Month Only Before It Turns "RED"
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 Replies!   View Related
Conditional Formatting: Box That If The Cell Populates "Completed" Or "X Required", Then Highlight As Green
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 Replies!   View Related
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

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

View Replies!   View Related
Change The Default Output Values "FALSE" And "TRUE"
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 Replies!   View Related
Custom Format Which Displayed "-"(Dashes Without Quotes) Insted Of "0"
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 Replies!   View Related
Using ChDrive And ChDir: Check If "PPG" Exists In "C:MEASURE-6000"
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 Replies!   View Related
Remove "TRUE" & "FALSE" Words From A Linked Checkbox
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 Replies!   View Related
Pivot Table Showed "All" Instead Of "Multiple Items" When Using Datasource From CSV
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 Replies!   View Related
Unable To Use "merge And Center","insert" In Unprotected Cells
I can choose some cells in a sheet to protect them and prevent any one to change.
it's supposed to change and edit in the other cells.

the problem which I face when I try to use " merge and center" or to insert picture or autoshapes ..etc. in unprotected cells!

View Replies!   View Related
Changing Default Settings Of Autofilter...change From "equals" To "contains"
When i record a macro with an autofilter, the default setting is always "equals". Is there any VBA code or anything to be able to change so that my macro will search in a "contains" setting.

View Replies!   View Related
Resizing Diagrams Without Getting A "compressed" Or "stretched" Result
I have several charts which I want to make the same size, without some getting "compressed" and others "stretched"?

My goal is in other words to get uniform diagrams.

View Replies!   View Related
If Then In Macro: If S2 Is Empty The It Will Skip Down To "Columns("W:X").Select"
I have this in my Macro:............

My Macro runs fine when there is something in Cell S2 but if there is not then I get an error on "Cells(sumrownum, 19).Value = "Total" 'Column S". What I want to do is put a if then type of statement that if S2 is empty the it will skip down to "Columns("W:X").Select" and keep going with the rest of my macro.

View Replies!   View Related
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()
End If
End Sub

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.

View Replies!   View Related
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"))))))

View Replies!   View Related
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.

View Replies!   View Related
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?

View Replies!   View Related
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 )

View Replies!   View Related
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

View Replies!   View Related
Copyright © 2005-08, All rights reserved