Free Microsoft Excel 2013 Quick Reference

VBA search for value and insert space two characters to the right

VBA search for value and insert space two characters to the right

First of all a friendly hallo to all Forum members.

Ok I've got about 20000+ lines of data that look like this:

1 -0.90520E-02-1.3410 -2.1393 2.1302 1.8640
2 0.77851E-02-0.92696 -1.9901 1.9979 1.7314
3 -0.34783E-01-1.1249 -2.0717 2.0370 1.7655
4 0.36477E-01-0.93091 -2.0064 2.0429 1.7700
5 0.83489E-02-0.92708 -1.9901 1.9985 1.7319

And I want to insert a space behind "E-XX" so it will look something like this:

1 -0.90520E-02 -1.3410 -2.1393 2.1302 1.8640
2 0.77851E-02 -0.92696 -1.9901 1.9979 1.7314
3 -0.34783E-01 -1.1249 -2.0717 2.0370 1.7655
4 0.36477E-01 -0.93091 -2.0064 2.0429 1.7700
5 0.83489E-02 -0.92708 -1.9901 1.9985 1.7319

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.

Any suggestions or help?

Thank you for your time!

the TUGuys

Post your answer or comment

comments powered by Disqus
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 above:

Sales1 Petter 552 moves to Row1 Col AA, Col AB, Col AC
Sales2 Mats 456 moves to Row2 Col AB, Col AC

Best regards


Is this possible if the data string in each cell is not uniform

Michael Maloney /J
Benhammer OddF
Xi Xiang Xu /

in the above example i am wishing to remove the characters in bold, what they have in common is they are the characters to the right of the last space in the string.
Can anyone help me.

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!

Hey gang,

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

While Len(Range("A" & CStr(LSearchRow)).Value) > 0
If Range("O" & CStr(LSearchRow)).Value = "D" Then

'Select DuctWork Header and copy
Sheets("BOM HEADERS").Select

'Paste into bom

'Move counter to next row

'Go back to Sheet1 to continue searching

End If

LSearchRow = LSearchRow + 1


Hi there,

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.

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

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

Can anyone help?

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

Thanks in advance for your help!

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

Thanks Again!

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


I'm trying to create a modification of the macro:

End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want to make excel move two cells to the right and then open the sheet name mensioned in the cell, my initial ideas are below, however this macro keeps displaying error messages.

ActiveCell.Offset(0, 2).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


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?

Any help greatly appreciated

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:
View this thread:

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.

Any help is appreciated.

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

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

Thank you.

hi all,
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 (blue)
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?

Self taught and over my head, I hope there is help.

I am searching for a value to be returned, that is working fine. My issue is if the value in the InputBox is not in the range.
I simplely need it to return to the InputBox and to start again with the correct value and message stating wrong ID number.

My coding below, appreciate any help.

Private Sub FindButton_Click()
    'Finds ID row and populates form
Sheets("Data Base").Select

ActiveCell.Offset(1, 0).Select

IDText = InputBox("Please insert ID issue number.", "Find client issue")

Selection.Find(What:=IDText, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate


Is there a way to take an input value to be searched in a particular column and on finding that value copy the entire row on to the new sheet with sheet's name same as that of the column entry.For eg
Column A Column B Column C Column D
as aa df dn
cv bb gfh dfg
xv aa dfg dfg
xb cc n cn

I want to search the column B for any value eg "aa" on finding it paste the whole row to another sheet named aa. On again finding aa it should be pasted below previous entry in the sheet aa

I have an Excel app that generates the dates for the month using NETWORKDAYS
across the top row. What I'm struggling with now is how to loop through all
the columns searching for "FRIDAY" and inserting a blank column to sum the
weeks numbers. Any help would be greatly appreciated. Thx

I am working on a spread sheet that searches for an employee's clock number, pulls the data from a particular week and displays it all on one page. I have 6 different tables of different stats, 4 weeks each and I would like to pull all of this data together. I have successfully created a page that when you plug in an employee's number, all of thier stats apear from all 6 data sets for the last 4 weeks. I do this by using

week 1

week 2

week 3

week 4

Credit CardsCustomersTotal AppsPer/1000week 13/8/2008192315.62week 23/15/200819315.18week 33/22/200813200week 43/29/200810519.52Totals62258.04

I have to account for data that is missing becasue they may not have worked, and then the devide by zero error. This works for me if I separate all 4 weeks into thier own cell, which is handy for the employee to see.

But I want to create a master employee sheet for the manager that combines 4 weeks into one cell. So vlookup up would collect all 4 values of total cards for the week and add them up to get 622. I might also want to devide by 4 to get the 4 week average in the same cell; but if the value is zero...

Maybe vlookup is the wrong function, but I haven't seen any other examples of what I am looking to have happen.

I have taught myself through till this point and 3 days of creating, I need some help for the final push. I've tried different combos and looked online... any suggestions?

I am not familiar with the Lookup or VLookup functions and so I'm not sure if those would be the best solution to my problem or if using a macro or another method would be better. I want to search for a value (ID) in a separate Excel file and then return another value in the same row to my worksheet. Basically the file that I am searching has many columns of data and I am using the ID column as a 'primary key' because it is unique. Then, after the search finds the ID value, I want to be able to move over a specific number of rows in order to return certain data. So for example: I want to search for "ID123" and then move over 3 columns and return "Data1" (which is in the same row as "ID123"). I was thinking that I could use a macro in VBA to search for the value and then move over a specific amount of rows and return the value. My problem is that I'm not sure how to do this when I am searching through a separate Excel file and I wasn't sure if it would work if that Excel file is closed. I'm also not sure if using a macro is the best method for this issue. I would greatly appreciate your help. Thanks

No luck finding an answer? You could always try Google.