I'm trying to solve a strange problem in a piece of code.
I have a variable that is define as Double called STD. When i try to insert that variable in a formula the decimal sign (for me a comma "," because I'm Portuguese) gets converted to ";" (which is for me the separation sign for the expressions in excel formulas. ex: AND(A1>0;B1>0)=TRUE). The code is:
I have a very frustrating problem with my business plan i am writing in Excel. every so often it crashes and when I open it again all the £ signs have turned to $ signs and i have to go through and painstakingly change them all. This also happens even if I just close the file carefully and open it up again. I have tried looking in preferences to see if there is a default setting but can't seem to work it out. can anyone help me with this tiresome issue?
I want to save phone no as +99 9876543210 in excel 2003 on my xp pro machine. But if i give a + sign in the cell, some blue dotted rectangle shows up and everything messes up.. I think it is treating it as a formula or something... how can i save this in the cell. tell me in detail if you are going to tell me about macros or vb code as I don't know how to insert code or program macros.
I got three columns in excel. Column A is a list of items, so is column C. Column B displays the similarity in the 2 lists.
I use the following command =IF(ISERROR(MATCH(A1,$C$1:$C$265,0)),"",A1).
SO: A B C
Cat Fish Dog Dog Roll Bull Carpet Apple Dog
But what I want to do is the following:
If I have "D-og 12 2-3" in column A and say "do g 1 22/3" in column C, I want it to be displayed in column B. To do that I need to decapitalise and remove all the special symbols such as space, ), (, *, &, ^, %, $, #, @,! etc.
So I get dog1223 in column B since they are the same.
I'm trying to simplify a section of my code by naming a long string that is repeated. I tried by simply place the Name before equal sign (=) and also putting Set before the name. Both failed though I've seen both methods used successfully in other code. Am I having a syntax issue of some sort?
Private Sub ConditionalRowDelete() Dim NumRows, iLine As Integer ActiveSheet.UsedRange.Select NumRows = ActiveSheet.UsedRange.Rows.Count Dim CircuitType As Range Set CircuitType = Range("C" & iLine).Value For iLine = NumRows To 2 Step -1 If Range("A" & iLine).Value > "6999" Then Rows(iLine).EntireRow.Delete End If Next iLine For iLine = NumRows To 2 Step -1 If CircuitType = "VOIP" Or CircuitType = "Customer Care" Or CircuitType = "Dialup" Or CircuitType = "IRU" Then Rows(iLine).EntireRow.Delete End If Next iLine End Sub
This should be an easy one but I am having a difficult time extracting the digits after the # sign in each account description in my list. The values in each cell do not follow any rhyme or reason and differ in length. Three examples of the current data and what I am looking to extract are below.
Current Data: ALBERTSONS #8272-ROSEVIL WHS-closed ALBERTSON'S #703 - SAN RAMON ALBERTSONS #7105 - CARMEL (SOLD 6/06)
Get a SUMIF formula to work, which it now does, but the formula puts '$' signs in the range, so that when it copies it, it does not change the range. I have got round this, by defining 12 separate ranges. But it does make the code rather long winded. Anyone know how to put the formula in without the '$' signs? There must be a way to put the formula into the first cell, and copy it across?
Set Rga = Range("b6") Set Rgb = Range("b6").End(xlDown) Set Rgd = Range("$e$6") Set Rge = Range("$e$6").End(xlDown) Set Rgc = Range(Cells(6, 2), Cells(6, 2).End(xlDown)).Offset(0, 3) Set Rgh = Range(Cells(6, 5), Cells(6, 5).End(xlDown)).Offset(0, 3) Set Rgi = Range(Cells(6, 5), Cells(6, 5).End(xlDown)).Offset(0, 4) Set Rgj = Range(Cells(6, 5), Cells(6, 5).End(xlDown)).Offset(0, 5) Set Rgk = Range(Cells(6, 5), Cells(6, 5).End(xlDown)).Offset(0, 6) Set Rgl = Range(Cells(6, 5), Cells(6, 5).End(xlDown)).Offset(0, 7) Set Rgm = Range(Cells(6, 5), Cells(6, 5).End(xlDown)).Offset(0, 8) Set Rgn = Range(Cells(6, 5), Cells(6, 5).End(xlDown)).Offset(0, 9).............................
I have three pages of daily data I am working with; orginal source data, manipulated data, and my output. My output data is a daily row of six columns containing 1, 0, or -1, based on my manipulated data (1,1,0-1,1,-1). I would like by row, sum the coresponding source data (5,4,0,-3,3,-4) by the positve 1s and a second column with the negative 1s. then average the column by the number of consituents (positive5+4+3 )/3=4 & negative;(-4+-3)/2=-3.5)
I have a column that tells whether an adjacent cell is positive or negative. I'm trying to come up with a macro/formula that will tell me how long (# of rows going down) a negative or positive sign repeats before changing.
So it would go down the column and count the rows where the same sign repeats. Once the SIGN switches from + to - or - to + the number goes back to 0. Essentially counting the cells that move in one direction (+ or -).
For example. if A1 = +, A2 = +, A3 = +, A4 = +, A5 = -, A6 = + then it would count A1:A4 and output 4 in B4, meaning that the numbers have stayed negative for 4 rows. For A4:A5 nothing should appear as this sign change we don't want to record. Same for A5:A6. I don't know how to make a formula/macro to identify a repeating SIGN, and then dynamically count all those rows until the sign changes. Does this make sense? I've been beating my head against the desk trying to figure it out with no luck.
I have a spreadsheet with cells defined as Euro & Dollars. I want to add an automatic conversion so for example, if a cell is defined as $ the formula will return the sum X 4 and if it is Euro than sum X 5.
I tried to use "if" combined with "search" (so if the cell contains "$" multiply by 4) but since the cell is defined as currency the $ sign does not appear and cannot be searched…
I have a number $2,173,980.08 in my excel worksheet. I try to find it using Ctrl + F $2,173,980.08 and excel couldn't find the number. What setting do I need to change in order for excel to find number with dollars sign and commas in it?