Free Microsoft Excel 2013 Quick Reference

Finding and Amending a record

Hello,

I am having trouble with what appears to be a simple task! I have a few
forms which I am creating (have previously been here for help on other
aspects of creating these!).

Basically, I have set up a 'menu' form which can take you to one of
three forms, the first form is working great, this one inputs to the
spreadsheet with the data which you input (then press a 'submit'
button). the next form is to go on to the next stage of the record
which is adding more data after 6 months (only two cells). The third
form isnt even nearly done as it is roughly the same as the second form
and once i have done that the last should be a breeze.

The trouble i am having is I have got to the stage where i need to look
up from a unique number to find the right line and then add the two
bits of information to that line, (and then go on to finding the next
record etc etc until you are done adding). I have tried to achieve this
with various snippets of code which others have kindfully added as
solutions to other similar problems but I cannot get anything to do
what i want.

Perhaps i am trying to go to far too quick as although i can learn
things as i go along, before 3 weeks ago i had never touched VB in my
life. I have only just learned the offset command and the xldown and
other small bits!

Perhaps someone could help me with the concept and perhaps explain what
the command does?

(I am pasting a line of code which i have tried to get to work to no
avail)

Selection.Find(What:=Interiminput.regint.Text, After:=ActiveCell,
LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlBycells,
SearchDirection:=xlNext, MatchCase:=False).Activate

Many thanks in anticipation.

Duncan


Post your answer or comment

comments powered by Disqus
Hi,

When I run the Add, Amend and Delete Button and try to Amend a record it will over write the second record in the table.

See Attached File.

i have a spreadsheet containing test results.
the participants details are added initially.
later i have to add their results.

Open to any suggestions
I thought of a 2nd sheet to add the details and then a lookup(PersonID and date)
VBA (macro) not too hot......but i have used the now function to add unique ID to each record.

Any suggestion would be appreciated..have a few ideas but not sure. I believe in K.I.S.S.

Problem solved many thanks

have a macro that finds and highlights a number (Using the find and replace function). Now I need to expand it to find and highlight a group of numbers (I can paste them into the macro or VBA code) against the numbers in column A. I know there is a way, possibly using case logic or looping but need some help as I am a novice with VBA. Here's what I have in the macro so far:
Sub ReplaceAndHighlight()
'
' ReplaceAndHighlight Macro
' Macro recorded 8/28/2008 by KrazyKasper
'
'
Application.ReplaceFormat.Interior.ColorIndex = 6
Cells.Replace What:=10862, Replacement:=10862, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True

End Sub


Hi,

When I run the Add, Amend and Delete Button and try to Amend a record it will over write the second record in the table.

See Attached sample File.

I want to do a find and replace a space with a carriage return in excel and
am having problems finding a way to do this

Can you find and replace a style in Excel?

Hello,
I am looking for code where I can find and replace a word in a given string. e.g.

I have data in range A1 to A50 which have values as below:

person_name name,
place_name name ,
animal_name name,
item_name name
.
.
. and so on.

Now the first string in A1 is as below

person_name name,

I want to replace second occurrence of "name" (which is after space) in this string with "size" and output should be as below:

person_name size,
place_name size
.
.
and so on

But what I am getting is

person_size size,
place_size size,
.
. and so on.

How can I stop the first occurrence of "name" which is with underscore from not getting replaced.

Can I use some delimiter property or make use of the underscore with the first occurrence of name??

Thanks in advance !!!
Abhi

Dear Experts,

I've a table of data and wonder whether or not it's possible to use Vlookup
or Hlookup to find and list multiple rows of matching records. Please see
example below:

Name Supplier Cost
Bk-Alpha ABC & Co 99.00
Bk-Beta 123 & Co 89.00
Bk-Apha XYZ & Co 95.00

From the above scenario, how do I go about to find "Bk-Alpha" and display
all matching rows, ie: show ABC & Co, XYZ & Co and so on?

I would really appreciate any kind help.

Million thanks.
Rich

Ok, sample file on it's way.

Biff

"Rich - SG" <RichSG@discussions.microsoft.com> wrote in message
news:93D35367-03FD-4F38-B65B-A1E2D5A22DB7@microsoft.com...
> Yeah sure, thank you. My e-mail addy is richmondchia@yahoo.com.
>
> Btw, for the following example, this is what I am looking for...
>
> Supposing table of data being:
> Name Supplier Cost
> Bk-Alpha ABC & Co 99.00
> Bk-Beta 123 & Co 89.00
> Bk-Apha XYZ & Co 95.00
>
> and when I key Bk-Alpha, it will show 2 rows of data as per following
> format:
>
> Bk-Alpha ABC & Co 99.00
> Bk-Alpha XYZ & Co 95.00
>
> Alternatively, if it can be done using other function or others, kindly
> let
> me know. I'll use any available or feasible means.
>
> Thank you very much,
> Rich
>>>>>>>>>>>>>>>
>
> "Biff" wrote:
>
>> Hi!
>>
>> Do you want to see a sample file? If so, post an email address and I'll
>> gladly send you one.
>>
>> Biff
>>
>> "Rich - SG" <RichSG@discussions.microsoft.com> wrote in message
>> news:3C325D66-038C-4833-9B20-16AD9D9C97FB@microsoft.com...
>> > Dear Biff,
>> >
>> > Can you elaborate more? I had already done it correctly and the formula
>> > did
>> > show the braces before and after the formula.
>> >
>> > If possible, can you give me a more detailed illustration how to go
>> > about?
>> >
>> > Thank you,
>> >
>> > Rich
>> >
>> > "Biff" wrote:
>> >
>> >> Post the *EXACT* formula that you used.
>> >>
>> >> Also, this is an array formula which means that after you type the
>> >> formula
>> >> instead of just hitting ENTER you *MUST* hold down the CTRL and the
>> >> SHIFT
>> >> keys, then hit ENTER. If done properly Excel will place squiggly
>> >> braces
>> >> { }
>> >> around the formula. You must use the key combo of CTRL,SHIFT,ENTER to
>> >> accomplish this. You cannot just type them in.
>> >>
>> >> Biff
>> >>
>> >> "Rich - SG" <RichSG@discussions.microsoft.com> wrote in message
>> >> news:4909385A-CD54-42C3-B290-E06CA14E3540@microsoft.com...
>> >> > Dear Biff, thank you for your prompt response.
>> >> >
>> >> > I had tried your method but it won't show the second value of
>> >> > Bk-Alpha
>> >> > which
>> >> > reads at 99 and 95. Is there any way I can use to display all values
>> >> > of
>> >> > column 3? ie; show both 99 and 95 for bk-alpha when chosen.
>> >> >
>> >> > Thank you once again.
>> >> >
>> >> > B/Regards,
>> >> > Richmond
>> >> >
>> >> >
>> >> > "Biff" wrote:
>> >> >
>> >> >> Hi!
>> >> >>
>> >> >> The ultra-easy way is to use a filter.
>> >> >>
>> >> >> Another way:
>> >> >>
>> >> >> Suppose your table is on sheet1 in the range A1:C4
>> >> >>
>> >> >> In sheet2 A1 enter: Bk=Alpha
>> >> >>
>> >> >> In sheet2 B1 enter this formula with the key combo of
>> >> >> CTRL,SHIFT,ENTER:
>> >> >>
>> >> >> =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A$1,ROW($1:$3)),ROW(1:1)))
>> >> >>
>> >> >> Copy across to C1 then down until you get #NUM! errors meaning the
>> >> >> data
>> >> >> has
>> >> >> been exhausted.
>> >> >>
>> >> >> Biff
>> >> >>
>> >> >> "Rich - SG" <Rich - SG@discussions.microsoft.com> wrote in message
>> >> >> news:DAC78C6C-C31A-423B-8B7A-57A2E09A305D@microsoft.com...
>> >> >> > Dear Experts,
>> >> >> >
>> >> >> > I've a table of data and wonder whether or not it's possible to
>> >> >> > use
>> >> >> > Vlookup
>> >> >> > or Hlookup to find and list multiple rows of matching records.
>> >> >> > Please
>> >> >> > see
>> >> >> > example below:
>> >> >> >
>> >> >> > Name Supplier Cost
>> >> >> > Bk-Alpha ABC & Co 99.00
>> >> >> > Bk-Beta 123 & Co 89.00
>> >> >> > Bk-Apha XYZ & Co 95.00
>> >> >> >
>> >> >> > From the above scenario, how do I go about to find "Bk-Alpha" and
>> >> >> > display
>> >> >> > all matching rows, ie: show ABC & Co, XYZ & Co and so on?
>> >> >> >
>> >> >> > I would really appreciate any kind help.
>> >> >> >
>> >> >> > Million thanks.
>> >> >> > Rich
>> >> >> >
>> >> >>
>> >> >>
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>>

Hi!

Do you want to see a sample file? If so, post an email address and I'll
gladly send you one.

Biff

"Rich - SG" <RichSG@discussions.microsoft.com> wrote in message
news:3C325D66-038C-4833-9B20-16AD9D9C97FB@microsoft.com...
> Dear Biff,
>
> Can you elaborate more? I had already done it correctly and the formula
> did
> show the braces before and after the formula.
>
> If possible, can you give me a more detailed illustration how to go about?
>
> Thank you,
>
> Rich
>
> "Biff" wrote:
>
>> Post the *EXACT* formula that you used.
>>
>> Also, this is an array formula which means that after you type the
>> formula
>> instead of just hitting ENTER you *MUST* hold down the CTRL and the SHIFT
>> keys, then hit ENTER. If done properly Excel will place squiggly braces
>> { }
>> around the formula. You must use the key combo of CTRL,SHIFT,ENTER to
>> accomplish this. You cannot just type them in.
>>
>> Biff
>>
>> "Rich - SG" <RichSG@discussions.microsoft.com> wrote in message
>> news:4909385A-CD54-42C3-B290-E06CA14E3540@microsoft.com...
>> > Dear Biff, thank you for your prompt response.
>> >
>> > I had tried your method but it won't show the second value of Bk-Alpha
>> > which
>> > reads at 99 and 95. Is there any way I can use to display all values of
>> > column 3? ie; show both 99 and 95 for bk-alpha when chosen.
>> >
>> > Thank you once again.
>> >
>> > B/Regards,
>> > Richmond
>> >
>> >
>> > "Biff" wrote:
>> >
>> >> Hi!
>> >>
>> >> The ultra-easy way is to use a filter.
>> >>
>> >> Another way:
>> >>
>> >> Suppose your table is on sheet1 in the range A1:C4
>> >>
>> >> In sheet2 A1 enter: Bk=Alpha
>> >>
>> >> In sheet2 B1 enter this formula with the key combo of
>> >> CTRL,SHIFT,ENTER:
>> >>
>> >> =INDEX(Sheet1!B$2:B$4,SMALL(IF(Sheet1!$A$2:$A$4=$A$1,ROW($1:$3)),ROW(1:1)))
>> >>
>> >> Copy across to C1 then down until you get #NUM! errors meaning the
>> >> data
>> >> has
>> >> been exhausted.
>> >>
>> >> Biff
>> >>
>> >> "Rich - SG" <Rich - SG@discussions.microsoft.com> wrote in message
>> >> news:DAC78C6C-C31A-423B-8B7A-57A2E09A305D@microsoft.com...
>> >> > Dear Experts,
>> >> >
>> >> > I've a table of data and wonder whether or not it's possible to use
>> >> > Vlookup
>> >> > or Hlookup to find and list multiple rows of matching records.
>> >> > Please
>> >> > see
>> >> > example below:
>> >> >
>> >> > Name Supplier Cost
>> >> > Bk-Alpha ABC & Co 99.00
>> >> > Bk-Beta 123 & Co 89.00
>> >> > Bk-Apha XYZ & Co 95.00
>> >> >
>> >> > From the above scenario, how do I go about to find "Bk-Alpha" and
>> >> > display
>> >> > all matching rows, ie: show ABC & Co, XYZ & Co and so on?
>> >> >
>> >> > I would really appreciate any kind help.
>> >> >
>> >> > Million thanks.
>> >> > Rich
>> >> >
>> >>
>> >>
>> >>
>>
>>
>>

Dear Experts,

I've a table of data and wonder whether or not it's possible to use Vlookup
or Hlookup to find and list multiple rows of matching records. Please see
example below:

Name Supplier Cost
Bk-Alpha ABC & Co 99.00
Bk-Beta 123 & Co 89.00
Bk-Apha XYZ & Co 95.00

From the above scenario, how do I go about to find "Bk-Alpha" and display
all matching rows, ie: show ABC & Co, XYZ & Co and so on?

I would really appreciate any kind help.

Million thanks.
Rich

Hello all,

I'm fairly familiar with Excel, but just starting to learn VBA. I have a little bit of experience messing around with VB, so hopefully, this will be fairly easy for me to pick up.

Anyway, I'm in the process of developing this spreadsheet solution for my work. What this spreadsheet does is tracks provide information to a team of people all over the world. we track certifications based on application, system, graphics card, OS, and driver.

So, the spreadsheet contains several rows with many columns. I created a userform that would add new rows with the above information. after awhile, this will become a pretty big spreadsheet with several hundred rows.

My question is, on my userform, when I select a criteria using the dropdown combo box (application, OS, graphics card, system) and then enter a driver version in the text box, could it do a search first to make sure I'm not duplicating a record. So, if I enter an application, OS, graphics, system that already is listed with a driver, I don't want to add it again unless I'm trying to update the driver information.

So when I click on Add, how do I make it search the existing rows to make sure that I'm not duplicating my efforts?

thanks,

Charles W.

I am trying to get excel to Find a cell in column A and then when it finds the cell delete the row. The problem is that I want it to search for data that is entered into another cell on a different sheet. I want to enter data into a cell and run a macro that will search column A on another sheet for data and then delete it's row. I have this code that I was hoping to manipulate, but I have no idea how to.

	VB:
	
 CriteriaDeleteRow() 
    Dim r As Long, LastRow As Long 
    LastRow = Range("A65536").End(xlUp).Row 
    For r = LastRow To 1 Step -1 
        If Cells(r, 1).Value = "bad" Then Rows(r).EntireRow.Delete 
    Next r 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I want to replace "bad" with a cell that has the data to search for. So if i change the value in the cell it will search for the new value. Any help would be appreciated. Thanks

Greetings all,

I have a find and replace question. I know the basics of find and replace but i am looking at doing something I am not too familiar with. I am trying to be able to input a value in column A and the string of text in column B will be affected by the value that I put in Column A. And i want to be able to do this with multiple rows with multiple values.

Example:
My string of text in column B remains the same until the value of Column A changes. The find/replace will overwrite the "" in the string.

This is my string of text in Column B: "^cCHT;all;;t;g;F1;F1;;all;;t;g;F2;F2;;all;;"

A1 value = "ABC"
Resulting Text in B1 = "^cCHT;all;;t;g;F1;F1ABC;;all;;t;g;F2;F2ABC;;all;;"

A2 value = "XYZ"
Resulting Text in B2 = "^cCHT;all;;t;g;F1;F1XYZ;;all;;t;g;F2;F2XYZ;;all;;"

I know that I can accomplish this by creating a button but I was wondering if there is a more automated way like a formula in a cell or something.

I have a macro that finds and highlights a number (Using the find and replace function). Now I need to expand it to find and highlight a group of numbers (I can paste them into the macro or VBA code) against the numbers in column A. I know there is a way, possibly using case logic or looping but need some help as I am a novice with VBA. Here's what I have in the macro so far:

Sub ReplaceAndHighlight()
'
' ReplaceAndHighlight Macro
' Macro recorded 8/28/2008 by KrazyKasper
'
'
Application.ReplaceFormat.Interior.ColorIndex = 6
Cells.Replace What:=10862, Replacement:=10862, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=True

End Sub

hey i'm sorry in advance for not doing a search, but i'm really crunched for time.

I was wondering if someone knows the code to run a find and replace across a drive (B:/) to replace a pdf file.

One of the guys had the wrong file name and it was replicated across multiple folders, so they need to fix it ASAP.

Thanks in advance,
JD

I am looking for a macro to delete each cell that has a value of "0". How would this be done? I tried finding and replacing, but that eliminated necessary 0's, such as in "10"

Thank you!

I have data in a the workbook that contains an asterik in it. I want to
clear that character and leave the remaining ones (or replace it with
something). But a * is considered a wildcard character that signals Excel to
grab everything. How do I find and replace just that?

I want to write a formula that will locate a word everywhere it appears in a
worksheet and boldface it. I know that I can use 'find' and then boldface
to accomplish this but I have a extremely large worksheet and would like to
speed up the process. My knowledge of Excel is not advanced enough to even
know where to begin.

Is there a way to do this?

Artis

I am trying to find and replace all special characters that are question
marks, or ? I can't seem to do a Find and Replace for those char, is thers a
special way i have to put that in the find and replace function? Thanks

I have some VBA that will find and select a row where value "G17" exists in column B.

I need to repeat this step until there are no "G17"s left in the sheet.

can anyone tell me how??

thanks in advance..

Hey there,

I'm semi fresh to the world of macro and function creation so bear with me please.

I have column A and B with 30 cells (A30 to B30) of data (text) and I want to find and copy the duplicate between them to cell C1.
(facts: 1) there's always one duplicate. 2) there's only one duplicate.)

How can I achieve this?

I've only tried simple stuff, nothing of placing a function inside a function, I'm way too basic for that yet.
I've done LOOKUP, VLOOKUP, 31 IFs and then I discovered that there's a limit of 7 (lol), a SELECT CASE, MATCH, FIND, SEARCH but to no avail. I've not managed to find a function that can directly make a match from an array to another array because the problem lies in the possibility of existing too many duplicates (which won't be the case here).

Hello all,

I'm sure this is an easy fix, but as I haven't used excel for a while and have had no luck looking searching the forum I'm going to need to ask for help

I need to do a kind of find and replace scenario, however I don't think I can use this standard function for my requirements. I've also tried the IF function without success.

I want to find all instances of the character * in Column A and replace it with the contents of the cell immediately to the right (in Column B).

ANy help, much appreciated.

Thanks

I need help with finding and then replacing a symbol in my worksheet. In the
find and replace box there is no way to insert a symbol there. I have tried
to cut and paste as well as to insert the symbol in the find box. If anyone
has an idea on how to replace a bullet with a comma please let me know.
Thanks
TC


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