At the moment the VBA code looks somewhat like this:
No = 20000
For i = 1 To No
Ra = "A" & i
ActiveCell.Replace What:="E-01", Replacement:="E-01 ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
ActiveCell.Replace What:="E-02", Replacement:="E-02 ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
ActiveCell.Replace What:="E-03", Replacement:="E-03 ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
ActiveCell.Replace What:="E-04", Replacement:="E-04 ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
ActiveCell.Replace What:="E-05", Replacement:="E-05 ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
ActiveCell.Replace What:="E-06", Replacement:="E-06 ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
ActiveCell.Replace What:="E-07", Replacement:="E-07 ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
ActiveCell.Replace What:="E-08", Replacement:="E-08 ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
ActiveCell.Replace What:="E-09", Replacement:="E-09 ", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
But unfortunately it has two major drawbacks. It is inefficient and thus quite slow and I have to edit the code every time I apply it to a new set of data depending on the number of lines.
So my ides for improvement are the following.
Find "E-" and insert a " " (space) two characters to the right of "E-"
No=(value in a specified cell)
Unfortunately my web based research hasn't been to successful and all problems I found that were similar to mine were solved with code that is way out of my novas league.
1. Search for a specific cell value and relocate the two cell to the right it !
2. Removing all characters to the right of the last space in a cell
3. Worksheet Scrolls Two Columns to the Right When Toggling Between T
4. Macro to search sheet 1 for value and insert entire row from sheet 2
5. Search for Value and Insert Value in different column if a hit is found
6. How do I search for values and update cell totals?
7. Macro to search column with last value and insert TOTAL column to the left
8. Macro to Search for Value and Copy/Paste to New Sheet
9. VBA code to find all of certain value, then select them plus cells to the right
10. Move 2 cells to the right and then open sheet
11. inserting decimal two places from the right
12. Inserting time in cell to the right
13. Insert 19 cells "Shift to the right" if cell contains "->"
14. Insert 19 cells "Shift to the right" if cell contains "->"
15. 2 Macro's: only vertical copy/paste action and copy-paste 14 columns to the right.
16. Pick from list or combo box and return definition in cell to the right
17. Search for a String and insert Value in another cell
18. Search for names and copy to cell
19. search value and insert row under value
Hi i have a couple of textfiles that i import to excel whit spacedelimiter.
The text file can look verry difrent
from time to time but one thing is always constant: Place, Name and Amount (se below)
Sales1 (Place) Petter (Name) 552 (amount)
And i want to take out specific values from the imported txt.
Textfile can look something like this:
021124 Kiruna Steinholz Sales1 Petter 552 Out
021125 Sales2 Mats 456 Gross Out Ok
Texfile in excel:
Is there any formula or vba macro where i can search for "Sale" in range (A1:Z1000) and relocate all the cells
with sale in it and two cells to the right to a new location lets say Col AA Col AB Col AC ?
Ex from the textfile
Sales1 Petter 552 moves to Row1 Col AA, Col AB, Col AC
Sales2 Mats 456 moves to Row2 Col AB, Col AC
I have a spreadsheet with 5 tabs. Every time I move from one tab to another,
Excel automatically scrolls the worksheet two columns to the right. This
hides the first two columns of my data. I only have 16 columns of data so I
can see all of it without scrolling over. How do I stop Excel from
automatically scrolling each time I toggle from one sheet to another? Any
help is appreciated. Thanks!
I am new to excel macros. I have plenty of vba experience so I think we could do this easily.
I have a spreadsheet with several tabs. the overall goal is this
Search for values in the Sheet called
"BOM" in the "O" column. If the O column says "D" then we need to go to the sheet called BOM
HEADERS and copy row 9 and 10 and insert them back onto the BOM sheet right above where you found the "D" in column
"O". Then i need to search BOM sheet for "p". if found insert the rows 34 and 35 on sheet "BOM
HEADERS" back onto sheet "BOM" above the first instance of a row that has "p" in the "O"
column. I am basically wanting to take these headers and paste them back on the first sheet so i can have a pretty looking
spreadsheet. it is too manual now and wastes lots of time. Once i have all my headers in place i want to copy all the
values from sheet "BOM" to sheet "PACKING LIST". once i have done this i want to delete all the rows that
have a column "A" value containing a decimal. So it will copy the values from sheet "BOM" and past them
onto sheet "PACKING LIST" and then on sheet "PACKING LIST" delete all the rows that have a value in the
first colum like 2.1. so all the packing list will show is whole number rows. I hope this is descriptive enough. I will
paste the code i have so far.
it wont let me attach the spreadsheet. keep getting an database error
Dim LSearchRow As Integer
'Start search in column 15
LSearchRow = 1
Len(Range("A" & CStr(LSearchRow)).Value) > 0
If Range("O" & CStr(LSearchRow)).Value = "D" Then
'Select DuctWork Header
'Paste into bom
'Move counter to next row
'Go back to Sheet1 to continue searching
I have a requirement wherein I will be doing a manual Find for employee names in Column A and then
insert a Yes in Column B if I find the employee name in the sheet. There can be many occurences of the employee name i.e.
multiple rows , so I need "Yes" in column B in all these rows where I find my desired employee name.
bottomline is, the second part should be automated, i.e. once the hit/s is/are found, it should insert values "Yes"
in column B in all the "found" rows.
Please can anyone help me in doing this? Can i do this using Macro
or should I use VBA? I am not so familiar with either of it, however. Thanks
Hello there! This is tricky one which I really can't work out how to write code for because it's too complicated for a
standard cell formula.
Basically it's a search loop which runs the following when the file is opened. I would put
it in the workbook_open sub but instead I want to have the sheet update before a main userform appears.
need it to do is:
Read each cell in column K from row 6 onwards to the last active row.
If a value is
found then store the value (let's say as variable x) and from that cell, add up each value starting in the cell that is two
columns to the left (column I) and one row down, onwards going down until a cell = "" is reached in that column
(contains nothing). The total values added in column I could be stored as variable y.
Once cell = "" is
reached, have x - y to make z and then the total (z)should display in the cell to the very left of the very original cell
that contained the value x.
Finally, if x = the value in the cell to the immediate left from where x is (so same
row but in column J), have that whole row from column A to L turn green, else have it turn orange:
Selection.Interior.ColorIndex = 4 [for Green, for example]
Then return to searching in column K from cell where x was used onwards until the very last row.
I am trying to write a macro that will copy all data from column A of Sheet1, paste/transpose it into row 8of Sheet2, and
then add a Total column (with a total formula in row 10) after the last column pasted that has a value. Everytime I run the
macro, I will have a different number of values in Column A from Sheet1, so having the Total column added after the last
value will be very helpful.
Can anyone help me with this? I have the copy and paste portion down, I just need
help with the searching of the last value in row 8 and inserting a total column to the left of that.
Could someone please assist me in developing a macro to do what is explained below. Your assistance is greatly appreciated!
Thanks so much in advance!!
Select Sheet Raw Data
Search for the first active cell in column M
Within that row containing the active cell in column M:
Copy and Paste the following cells onto a new sheet in the first blank row on Sheet “Research”
-Value in column A
(from sheet Raw Data) gets pasted into column A on the first blank row (paste into sheet Research)
-Value in column F (from sheet Raw Data) gets pasted into column C on the first blank row (paste into sheet Research)
-Value in column M (from sheet Raw Data) gets pasted into column B on the first blank row (paste into sheet Research)
The macro would then loop and continue to search for values in column M and repeat the above steps
Looking at my attached will help.
I have race data, with the race # in the first column (Y to AC) (I am excluding the data in AD &AE.) Each race needs to get
pasted next to the black box, as race #1 has been done, in row Q.(The data goes next to the black cells, so that it can be
fed into the farther left area that analyzes the data. I already have another macro that does that) I just need to get each
race group lined up with the black markers, so the other macro knows where to find them. I was Pasting, but I have 3 tracks
I do everyday, and it gets a little tedious.
I'm very new to VBA, but I think, for instance it needs to find all the 1's then select over to the right 4 more cells to
encompass the entire race one data, excluding AD and AE. Once this is slected it just needs to get pasted next to the black
cell as race 1 is. Race 2 would go down next to the next black cell, then race three etc.
I've tried searching the internet, but couldn't figure out how to do this. Any help would be greatly appreciated. CC
If you have a number in the cell such as "100" or "25000" or "49" and you
want to place a decimal point two places from the right so that the numbers
read as "1.00" or "250.00" or ".49" respectively, then what Excel formula
would you use?
I tried =LEFT(P2)&"."&RIGHT(P2,2) but the problem with that is that it made
"25000" into "2.00". That's not what I wanted. I wanted "250.00".
Hi, i am working on a solution whereby once a value is typed into a
cell I need the 'cursor' to move to the next cell to the right, then
input the date and time into that cell, then return the 'cursor' to the
next row down
Ie info is typed into cell B2, I need the current time and date to be
automatically put into cell C2, then the 'cursor' needs to return to B3
etc. Is the easiest way to do this by recording a macro?
I have been looking for a sub that can find a cell in column A that ha
a "->" in it the actuall cell will contain "P->9999999" the only thin
consistant is the "->". When it finds the cell I need it to shift 1
cells to the Right.
Thanks for the help or any ideas,
robertjtucker's Profile: http://www.excelforum.com/member.php...fo&userid=2180
View this thread: http://www.excelforum.com/showthread.php?threadid=38933
I have been looking for a sub that can find a cell in column A that has a "->" in it the actuall cell will
contain "P->9999999" the only thing consistant is the "->". When it finds the cell I need it to
shift 19 cells to the Right.
Thanks for the help or any ideas,
For a company planner I am looking for a marco that copies a cell range (already merged) to a position vertically somewhere
above or vertically below (with no horizontal shift!).
It starts with question "select range to be
copied" then "select destination"
If the user clicks a destination somewhere aborve or below, then
macro pastes the selected cell range exactly vertically under it, eventhough the uses clicks with a horizontal shift.
A second command button does the same in horizontally, with the different that the copied cell range is always 14
columns to the right. Consists of only one question: "Select cell range to be copied to next day"
I'm having difficulty doing what I think should be something very simple to do in Excel 2007.
In my attachment, I
have a worksheet where I need to pick a rating from a combo box or list (Expert, Leading, Applying, Learning, Under
Performing). I will be picking this rating 10 different times on this worksheet and the selections will not be the same in
all cases so the definition I need won't always be the same.
I would like to return the definition (definitions
are detailed on another worksheet, Data Elements) to the cell to the right.
Can someone get me started here? I've
tried IF statements but when I make one selection from the combo box, all the combo boxes select the same thing. I've also
tried vlookup but can't figure it out and can't find an explanation in layman's terms.
I have a big Excel file (over 8000 rows) with repeating data. Instead of adding information by hand I feel some VBA coding
would come in handy .
I need help wring a macro that Searches for a particular string in the 2nd column. When it
finds it, it inserts a value in a cell 3 columns to the right on the same row as the found string. This should continue until
there are no more strings matching the search criteria.
I have two tables:
> Table_1 is a list of vendors. E.g. TechnoGadget
> Table_2 is a table with the list of monthly transactions. There are 4 columns. The number of rows vary from month to
Column B in Table_2 lists the description of each transaction, which includes various pieces of
information, such as item purchased, location, the vendor’s name, and other info all combined in one phrase.
E.g. In the month of July, there were two transactions from TechnoGadget”and the descriptions listed as:
1) - Product A Location VEN Code 2345 TechnoGadget
2) - Product B Location R5 Code 120090 TechnoGadget Return
During any month, there could be several vendors and
several transactions from the same vendor.
I would like to insert a new column after Column B in Table_2 which
shows the vendor’s name only.
Can this be automated using VBA so that macro will search through each transaction
in Column B of Table_2 , look up for any recognizable name from Table_1 and, copies that name in the corresponding cell to
the right in the newly created Column C of Table_2
can you help me with this?
i search a macro which will do following.
I have a range of cells (old data) and other range of data (new data -
those are locatet under the old data). I want after i select my new range of data (only yellow data) and running macro look
in to the old data (blue) where can by blank row too and looking for the same id (id_new=id). If it found any, it will insert
row under the last equal ID value (in old, yellow data) and copy the new data (whole row) in the created row in new data
Here is atach. with 2 sheet. Sheets with name "Before" and "after".
I want to use it for a
huge data (about 6000 rows and 80 columns)
The range of old data will be always ends 5 rows upper the new data
Please how can i do this?