Free Microsoft Excel 2013 Quick Reference

I just accidentally FIND & REPLACE a text which is part of a filename of a external source file...

And there are literally tens of thousands of cells in my file linked to that external source..

Excel now asks me to UPDATE VALUES and although all I need to do it click CANCEL, it is still way too much clicking.

Is there an easy way to get out of this, without having to terminate excel (as I have not saved the file yet)?

Thanks


Post your answer or comment

comments powered by Disqus
I am new to the VBA for Excel programming. Any help and any advice will be very very helpful.

I have several worksheets in a workbook. in all of them I need to find cells containing text (it is part of the string in a cell) and copy all rows from all the sheets containing these cells into one worksheet one set after another.

Any help and any advice will be very very helpful.

Dear Friends,

How to remove the Underline from a Text which is linked to another slide in MS Powerpoint 2007?

Please do help.

Hello All,

I'd like your advice on to use the functions substitute or replace in excel with few examples. Of course, I already went to the Excel Help base, but their examples didn't suffice me.
Basically, let's assume I want to replace a specific word in a cell which is comprised of two words, or comprised of only one word.
What functions would work best to do that and would this work on numbers as well?

Assume that:
A1: Paris France ---> How to you remove Paris in the 1st place and Secondly, How do you keep Paris and remove France.
---------And
A2: Paris France ---> How do you remove Paris and replace it by Monaco (keeping France),

Thank you guys, any link to this matter would be much appreciated

Hello all

I hope my question makes sence

I have a large number of workbooks containing a varying number of sheets,
which are updated each month by people. One part of the sheet references
back to the previous month's to obtain the value of a specific cell

My problem is this. Each month I have to create a new set of workbooks in a
new folder (Called Month XX), but obviously the formula looking back to the
previous months cell is now looking in the wrong folder and so I have to
change each formula by hand to point it to the new folder (This is Month 7,
the workbooks in Month 6 are referenced to Month 5, but copied now into Month
7 are STILL referenced to Month 5)

What I thought of doing was have the folder name, i.e Month XX, as a text
string in a cell (which can simply be changed each month) and have the
formula pick that value up and use it. My formula is currently:

='W2knas1com-bm$2006-2007Month 6[Assessment.xls] 4180 - Fieldwork
Teams'!$G$13

I want the Month 6 part of the formula (which is part of the external
reference path) to get that little piece of information form say Sheet1!A1

Then in Sheet1!A1 I can type Month 6, then next month type Month 7 and the
formulas all look to month 7

I can not seem to work out how to do this (if it is at all possible to) and
so would appreciate any suggestions

Thank you

Prior to 2003 one could "find & replace" a text string with a text string
that was formatted; i.e. bold or "red" text. Now it appears that when you
select formatting for the replace box it applies it to the entire cell and
not just to the replacment text string. Am I overlooking something? Can I
still just replace text with a formatted text string

Prior to 2003 one could "find & replace" a text string with a text string
that was formatted; i.e. bold or "red" text. Now it appears that when you
select formatting for the replace box it applies it to the entire cell and
not just to the replacment text string. Am I overlooking something? Can I
still just replace text with a formatted text string

HELP! What happened to the funtion to "find & replace" a text string with a
text string that was formatted; i.e. bold or "red" text?

Now it appears that when you select formatting for the replace box it
applies to the entire cell and not just to the replacment text string as it
did for years prior to 2003 Prof.

Am I overlooking something?

Can I still just find & replace a text string with a formatted text string?

HELP! What happened to the funtion to "find & replace" a text string with a
text string that was formatted; i.e. bold or "red" text?

Now it appears that when you select formatting for the replace box it
applies to the entire cell and not just to the replacment text string as it
did for years prior to 2003 Prof.

Am I overlooking something?

Can I still just find & replace a text string with a formatted text string?

Hi

I have a table form 1-99 where each value is relative to a text string.

My cell shows a number which is "52" from a table of ( 1-99)
Each numerical value beteen 1 to 99 equates to a string of text and i would like the adjacent cell next to the numerical value of 52 to read "water injection" which is from the table also, what formula can i use to read the correct text which is relative to the number ? and what formula do i use to link the numerical value to the text in the table?
Thanks mike

Hello,

I'm a bit (ok a lot) of a newbie with Excel macros and I'm trying to write a script which scans a worksheet for text enclosed in and converts the text in between to be bold and then removes the . I've managed to do this, except for one small problem. I keep the text I'm building in an spare cell and format the bit I want in bold as I build them so the text might look like this :

hello I am bold

This works fine, but when I then add a character to the text using the following command :

Sheets("working").Cells(2, 2) = Sheets("working").Cells(2, 2)+ Mid(word, i, 1)

It immediately removes any bold formatting from the text. I think this is because the line above is assigning the text value of the cell + the text value of the extra bit and then putting this back into the original cell. Can anyone tell me how I can just append a character to an existing cell's contents and not lose the formatting from the original contents ?

many thanks in advance

Cheeseboy.

Hi

i have a spreadsheet exported from sap with customer numbers that appear like numbers on this exported file. I also have an internal report that had been modified earlier in the year with the same customer numbers that appear like a text (which is a number with an appostrophy in front of it, looks like '123456). When i do the lookups, text doesnt recognise the number and visa versa. How do I change it? preferably either way?

Many ta's

I'm trying to this simple equation which I'm getting stuck on.

I would like to do an if statement where if cell contents equals that of another cell which is part of a list then it should match the cell corresponding to that group.

This was my attempt in going in a big circle with if statements which doesnt work... =IF(D34=K36:K65,IF(Trnx!D34=Sheet1!A1:A9,Happy,IF(Trnx!D34=Sheet1!A14:A16,Sad,IF(Trnx!D34=Sheet1!A18:A26,Neutral,IF(Trnx !D34=Sheet1!A28:A36,Grumpy,Mad)))))

here's an example of what i mean.

NameStatusThe ListFeelingsDaveMadJohnHappySteveJasonMadJamesKarenSadJasonKevinNeutralKevinDaveMad

so what happens is... Status is where I would like the output data. so it will look at the first name Dave. then check to see if it is listed on the list and when it finds it, it will output the feeling associated to it.

Can someone help me with this?

Thanks.

I'm trying to create a variable which is pulled from a cell in a worksheet, currently it has the fullname of the file, so Repbook.xls

With a sub I'm trying to remove the .xls component of this?

I'd started off in the direction of;

Code:
Dim RepBookToUpdate As Variant
Dim RepBook As Variant
RepBook = Range("C49").Value
RepBookToUpdate = ???
Is there a quick an easy way for this?

edit: from just the Excel side of it

Code:
gives me just the component I need - can I grab that via VBA easily?

Thank you for your time.

Hi All,
I need to see if a specific cell has a text entry which is part of a text array.
For example, if the text in A1 is 'Red' or 'Blue' or 'Green', then retrun a certain value, let's say 5. If A1 is 'Yellow' or 'Orange', return a 3, otherwise, return a '9'.
I cannot nest iF's ( I have ~ 12 values in the array) and I understand the OR statement does not accept text values.
I would rather not type arrays in a range but directly in the formula - if possible.
Thanks a bunch!!
ek

Does anyone know if this is possible? I print my worksheets in landscape and
need a text at the top ( portrait speaking) of my page. At the moment I am
having to manually write the text. The reason I am attempting to insert a
text box is so that I can changed the alignment and have it print "portrait"
when my worksheet is printing landscape. Hope that makes sense. :-). If
anyone can help or suggest another method, please let me know.

Thanks,
Pam

Simply stated, how would I remove all space in a text string.

Befo AA 123 ZZ
After: AA123ZZ

Excel 2003

Thanks,
dn

Hi I was just wondering if I could use a formula to delete part of text in a
cell after a certain string of text appears within that cell where the number
of characters before the certain text varies for example:

Big Fat Cat-brown-2803
Small dog-brown-705
Large sheep-brown-106
Small cat-brown-1803

I was looking for a formula so that everything after "-brown" and "-brown"
itself is is deleted. I can only think how to do this where there is a
consistent number of characters before or after the phrase I want to delete,
but unfortunately I don't have that luxury.

Any advice would be greatly appreciated!

How do I apply conditional formatting to a text box?

Does anyone know if this is possible? I print my worksheets in landscape and
need a text at the top ( portrait speaking) of my page. At the moment I am
having to manually write the text. The reason I am attempting to insert a
text box is so that I can changed the alignment and have it print "portrait"
when my worksheet is printing landscape. Hope that makes sense. :-). If
anyone can help or suggest another method, please let me know.

Thanks,
Pam

Hi

I wanted to know how to generate a date which is x number of working days before another date.

I have two cells, one which shows a date and one which shows the number of work (business days) days.

In other words I wanted to find out how to get a date which is x number of BUSINESS days (not including weekends)from a certain date. So for example If, I want to excel to generate a date which is 9 business days before 26 November 2007...how would I do that?

I hope I have not confused all you experts out there.

PLEASE advise.

Many Thanks!

Hi I was just wondering if I could use a formula to delete part of text in a
cell after a certain string of text appears within that cell where the number
of characters before the certain text varies for example:

Big Fat Cat-brown-2803
Small dog-brown-705
Large sheep-brown-106
Small cat-brown-1803

I was looking for a formula so that everything after "-brown" and "-brown"
itself is is deleted. I can only think how to do this where there is a
consistent number of characters before or after the phrase I want to delete,
but unfortunately I don't have that luxury.

Any advice would be greatly appreciated!

Hi all,

How can i insert an image into a text file and can i resize that image??

Hi,

See Attached

There is probably a very simple answer for this. All i am trying to do is hide the text which is on the axis of this radar chart...i.e 0,1,2,3

I try to clear it but that takes away the axis too.

Hope you can help,

thanks!!!!

First post here but have been browsing for a while
I am just getting into VBA mainly as a hobby so bear with me.

I have attached a form which is part of a larger form I am working on.

The user enters a heat output in the shaded text box and depending on the tolerance factor (default of 10%) a upper and lower range is calculated and displayed.

What I cant get to happen is when the tolerance is changed using the spin buttons, the upper and lower ranges are not updated.


	VB:
	
 SpinButton1_SpinDown() 
    txtTolerance.Value = SpinButton1.Value 
End Sub 
 
Private Sub SpinButton1_SpinUp() 
    txtTolerance.Value = SpinButton1.Value 
End Sub 
 
Private Sub txtOutput_Change() 
    Dim upper As Integer 
    Dim lower As Integer 
    On Error Resume Next 
     
    OnlyNumbers 
     'calculate the values
    upper = txtOutput.Value + (txtOutput.Value / 100) * txtTolerance.Value 
    lower = txtOutput.Value - (txtOutput.Value / 100) * txtTolerance.Value 
     'display the values
    txtUpperLimit.Value = upper 
    txtLowerLimit.Value = lower 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I have tried running the same code for the txtoutput_change event against the txttolerance_change event but it doesnt work right

Any ideas?


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