I am being passed a series of positive and negative numbers in a column - the only problem is that the negative numbers have the - at the end of the series i.e.1234-.Is there someway I can change this with a macro so they read -1234.
View 3 RepliesI have a workbook with a bunch of sheets and I am interested in the sheets that begin with the letter "P". Each of these sheets is named "P###" with #s ranging from 0-9 and not consecutively.
On each of these sheets, the various formulas link to the cell O37. I would like to replace the value in cell O37 with the name of the sheet "P###" for all sheets that begin with P.
In column A, I have many entries such as AA-1, AA-2, BB-1, BB-2, AA-3, etc.
How do you count the number of entries for only those beginning with "AA" or beginning with "BB"?
I need to put in a list of telephone numbers
i.e 01 is the std and 12345 is the number.
I need it to look like 0112345 but excel automatically removes the zero at the start of the number.
I have the following macro........
Dim i
If ActiveSheet.Index = Sheets.Count Then Exit Sub
For i = ActiveSheet.Index + 1 To Sheets.Count
If Sheets(i).Visible = xlSheetVisible Then
Sheets(i).Select
Exit For
End If
Next
This macro starts at which ever sheet I am on and goes from front to end say 1 to 16.....my question is how do reverse the code to go to the next unhidden sheet from 16 to 1 ?
I have a column with data and I want to add, in each row, the following:
at the beginning of row:{color=blue}
and right at the end of the row: {/color}
so that
excel
becomes
{color=blue}excel{/color}
I tried to do this with the formula:
"{color=blue}"&A1&"{/color}"
which yields nothing.
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.
View 3 Replies View RelatedI have some code that adds worksheets to a workbook and names them appropriately, I now need to change that to add the worksheets at the beginning of the workbook and am struggling with the syntax.
My first sheet is sheet1.
I need to change this piece of code
Sheets.Add After:=Sheets(Sheets.Count)
I have a column which contains a series of alphanumeric characters some start with the alpha characters and others end with them.
As an example ABS1234, 1234ABS, 12k34AB needs to be ABS1234, ABS1234, AB12k34. The number of alpha characters at the end will be a minimum of 2 and a maximum of 3. Is there some way that I can move the Alpha characters, at the end, so that they are always at the beginning of the string?
I have lost the formula for this looked everywhere, I have Content that looks like this e.g 1dg, 15gh, 8fj, 20lk,
I reqiure the formula to split the number into another cell(numbers will be no more than 2 digits), I already have the formula for the letters.
I want to add blank spaces to a cell with text
My cells have up to 14 spaces
If a cell as 1 or 2 characters -I want to add 6 spaces in front of text.
If a cell as 3 or 4 characters-I want to add 5 spaces in front of text.
If a cell as 5 or 6 characters-I want to add 4 spaces in front of text.
If a cell as 7 or 8 characters-I want to add 3 spaces in front of text.
If a cell as 9 or 10 characters-I want to add 2 spaces in front of text.
If a cell as 11 or 12 characters-I want to add 1 spaces in front of text.
I have the code below to add my new sheet and give it todays date (coming from the menu sheet). However I can not figure out how to add the before code so it will save the new sheet at the beginning of all of the sheets every time.
[Code] .....
I am trying to create a list of customer credits for my store. I have the person's name, the date of their credit, and the amount of the credit. This list will eventually be substantial and I would like to know how I can set it up so that when I open the worksheet I can enter new names from the top of the list and have the rest of the list shift downwards. This would stop me from having to scroll to the very bottom to add new info every time.
I would like to have it set up this way so that eventually I could put a search query at the top of the page (i'm thinking of rigging something up using vlookup) so that when my employees want to access the data base they can easily search a name and have the relevant info brought up, or can easily type in new store credits.
I have a simple code that should insert the text at the beginning of the text file (the text file already has some text in it). When i use the following code, it just appends the line to the end of the text. I need it to append it to the start of the text file.
Code:
Private Sub CommandButton1_Click()
Dim file As String
lastrow = Range("D65536").End(xlUp).Row - 4
file = "C:Users11126923Desktop est.txt"
Open file For Append As #1
f = "hi test3"
Print #1, f
Close
End Sub
In VBA, does anyone know how I can find the beginning and end of a named range?
eg: Range "CellsToTotal" is A1:A10, if I know only the name of the range, can I retrieve both A1 and A10 values?
What I have done is named a range on the fly as so (where vStartCellCoordinates and vEndCellCoordinates are both variables holding 2 ints):
ActiveWorkbook.Names.Add Name:="CellsToTotal", RefersTo:="=" & Cells(vStartCellCoordinates(1), vEndCellCoordinates(2)).Address
What i'd like to do is then set a third arbitrary cell to sum up the range as a formula (note the following is a wierd adapted pseudocode thing):
Cells(iRow, iCol).Value = "=sum(" & start(Range("CellsToTotal").Address(0, 0) & ":" & end(Range("CellsToTotal").Address(0, 0) & ")"
How can I make this work?
Is there a snatch of macro that can assign two variables with the date for the beginning and ending date of the current week?
I need
A= 4-19-2009 12:00AM
and
B=4-25-2009 11:59 PM
Based on the current week being week 16, with the variables changing as the weeks progress.
I need to make the active cell go to the beginning of the row (Column A). I tried setting up a variable to be the row.value, but that didn't work (the way I wrote it, which doesn't surprise me).
View 6 Replies View RelatedI would like to begin a loop with the current year, and then go down to 2000. I just can't figure out how to put the current year in there.
Sub YearCountDown()
Dim fyear As Integer
fyear = year(Now) + 1 'This does not work??
Do
fyear = fyear - 1
'REST OF CODE
Loop Until fyear = 2000
End Sub
When I run the following code, if "If exists = True" then I want the routine to start again at the very beginning. Currently, 'as is' it re-starts at the beginning but enters a loop on itself; I understand why this is but it's not what I want but can't get round this! How do I make the "If exists = True" condition (if true) restart the routine - ie call AllNEWActions() without then going into a loop on itself? NB: I can't use "Exit Sub" (see it commented out) as I don't want to exit routine, just restart it.
Sub AllNEWActions()
ShowCalendar
GetDateFromCalendar
SheetAlreadyExists
If exists = True Then
boolRestart = True
AllNEWActions
' Exit Sub
Else
MsgBox (" Date selected/new sheet doesn't exist")
InsertNewSheet
End If
If boolRestart = False Then
ShowCalendar
GetDateFromCalendar
End If
MsgBox ("Do something ...)
End Sub
Having difficulty with a VLOOKUP that uses a text lookup field beginning with 0. For all the other numbers formatted as text I use this function:
[Code].....
For the one that begins with a 0 I have to use this function:
[Code] .....
This is a problem for me, because I need to be able to copy the top function to all of my sheets without having to edit the ones where the lookup field begins with 0. I could alter my VBA to specifically search for those cases and adjust, but that seems unneccesary.
More info - The beginning column of the Table Array (i.e. the lookup field I'm searching for) is a function of another cell; both cells are formatted as text. Here is the function:
[Code] ....
I have to put a single quote in front of the lookup field, so that "064" is not displayed as "64" even if the cell is formatted as text. I have another instance where I am looking up "'791" and it works fine with the first VLOOKUP above. Even if I remove the single quote this will work. However, once there is a leading zero the function no longer works and I must use the second VLOOKUP listed above.
I'm compiling several old worksheets into a single database for a research study. The worksheets contain patient data. Some of the worksheets have the patient's last name, first name, and middle initial entered all in one cell like this: Smith,John R. Compounding the problem is the fact that sometimes the name is entered with a space between the comma and the first name, sometimes not.
I have formulas to break the name out into three separate columns "lName, "fName", "midInit". However, if the name in the original cell has a space between the coma and the first name, then the “fname” column will contain a blank space in front of the name. This is problem because patients names can appear in the database more than once. Some patients are in the database several times.
If patient “John R. Smith”, for example, is entered in the database as”
“Smith” “John” “R “
and also as
“Smith” “<space>John” “R”
then the database won’t recognize them as the same name when I search for John Smith’s data. Right?
If so, then I need a way to eliminate the empty spaces in front of the first names. Like I said, some have empty spaces and some don’t. I could do this by hand, but there are over 1000 entries in these worksheets.
I need to add zeros to the beginning of string of numbers in column A (given below), for e.g., 000029, 002562, 015096:
A
29
2562
15096
286
1
I have many excel wordlist, which are being converted from other file formats. Now there are sometimes useless spaces before entries in the cells. There are also some times commas and other not needed signs at the beginning of a cell in a column.
I need a macro, which removes spaces, symbols, letters, numbers or combination of them from the beginning of all cells in a column. The symbols should be chosen before the macro starts its work.
For example:
Before running the first macro:
Column A
,Word1
,,Word2
Word3
,Word4
After running the macro the beginning should be cleaned from chosen symbols, spaces or signes. (in our example if a cell begins with , then the commas should be deleted).
Column A
Word1
Word2
Word3
Word4
In any cell of a worksheet, the user can input a number followed by either a plus or minus sign. After the - or + key is pressed, the cell has to move the plus or minus to the front of the number, then move down one cell. So input in cell A2 would be 125+, that needs converted to +125 and then moved down to cell A3.
View 3 Replies View RelatedIs there a way to rename every file in a folder and subsequent sub-folders to begin with a number in memory? Say my number in memory is 5000, I'd like all the files to be renamed something like this:
5000Picture.jpg
5000bookreview.txt
5000startmanual.img
5000HighFive.mov
-->5000SUBFOLDER01
----->5000SUBFOLDER02
I have a column of Supplier IDs. Maximum number of characters is 9. If the supplier ID is not 9 characters long, it should have 0's placed in the front so that there are 9 characters. For example, Supplier ID is 487695. It should look like 000487695. I can get Excel to format the cells correctly by using the custom format. However, when I import into Access, it trims the 0's away even though the field is identified as text because the custom format does not actually change the "value" of the cell. It merely formats the cells.
How can I get this column to correctly import into Access from Excel?
I have a very long list of international phone numbers in a .csv format that I need to put a zero in front of each one. We are doing an international autodialer campaign for an upcoming event and need the zeros at the beginning to pay for the call. How do you get around Excel not allowing a zero at the front of a number and the .csv format not allowing you to save certain formats? I've been searching the archived posts for a hour now and can't find anything on this!
Here is an example
1.14478E+13
1.1448E+13
1.14417E+13
1.14421E+13
1.14421E+13
1.1448E+13
I am trying to get rid of the spaces at the beginning of text that is the result of a download from a reporting software package. I am using the Trim function but it does not work. It seems that it has something to do with the formatting. The first set of data of the download looks as follows:
SalesSales BreakdownSolenaceousCucurbitsLargeSeedIf I overtype the text in exactly the same way (thus keeping the spaces at the beginning), the trim function works. But this is not a practical solution.
The type of the cells is "2". I have tried copying the data as values to a different workbook but this does not help either.
I guess this is a simple one but I am not sure of the best way to do it,
I have number of points recorded for everyday in an excel sheet where first column represents date, second column represents group third column represents line and fourth column represents point as shown in the example below
3 19 2222 4324
2223 4325
2224 4326
2225 4327
2226 4328
"3" and "4" represent the 3rd and 4th day of the month, "19" represents the group and 3rd and 4th columns rest are line and point numbers recorded (2222 is first line of day 3, group 19 and 4324 is first point of line 2222 of group 19 on 3rd day and so on....).
I need a macro (or formula) to copy the cells of the last day written in the sheet up to the last row to a new excel sheet, reformat them so that first column has the word "Date" in its first cell, second column has the word "group" third has "line" and fourth has point.
so in the example mentioned here I need it to copy the cells of day "4" including the group, line and point numbers and format them in the new sheet so that first column has "DATE" then 4 for the rest of the rows, second column has "group" and 19 in the rest of the rows, 3rd column has "Line" and all line copied up to last row, 4th column contains "point" and all points copied.
to modify this code that prints each sheet to a different printer on the network. I would like for the code to print a sheet that will be named "MEMO" at the beginning of each print job....
View 9 Replies View Related