Thanks guys. It works great. Btw, how would I add color to & Sheet1!A1 once it inserts the word from this cell??
QuoteDisplay MoreOriginally posted by WillR
In Sheet 2 A1 place the following
="This animal is a " & Sheet1!A1 & " and..."
Hey presto...
Hope this helps,
Will
Is it possible to reference a cell that contains a word to into a cell that has a sentence?
For example:
Sheet 1
Cell A1: Dog
Sheet 2
Cell A1: This animal is a _____ and....
I'd like to insert whatever is in Sheet1 Cell A1 into this blank that's part of Sheet2 Cell A1.
Can this be done? I know this is a beginner questions but thanks in advance.
Here's what the code looks like:
------------------------------------------------------
Test = Range("A1:A20").Select
Set Test = Columns("A:A").Find(What:=SearchItem, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
If Test Is Nothing Then
AddressTemp = Test.Address
Do
Set Test = Worksheets("Sheet1").FindNext(Test)
Loop While Not Test Is Nothing And Test.Address <> AddressTemp
Else
Test.Activate
End If
------------------------------------------------------
The error I get now is the statement in red. I can't seem to get the FindNext command to pick up where it left off.
Well I have tried what you guys have suggested and setup a test to see if SearchItem is found. While using the code you suggested Derk, I get a 'type mismatch' error in this part of the code:
------------------------------------------------------
Set test = Columns("A:A").Find(What:=SearchItem, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False)
------------------------------------------------------
SearchItem is declared as a String, what should Test be declared as? Thanks again.
Thanks for the reply Richie. Here is the code I'm using now:
------------------------------------------------------
Columns("A:A").Select
Selection.Find(What:=SearchItem, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
------------------------------------------------------
This code is nested in a Do While Loop and stops when the list is completed. My question is how can I skip a SearchItem not found and continue to search the rest of the list? With this code, an error occurs once an item from the list is not found. Thanks for any help.
I am having problems using the find/search function in Excel using VBA. I am comparing 2 list using the find/search function but once an item is not found, an error occurs. How can I have the program highlight the unfound item and continue to search the rest of the list? Thanks for any help.
Thanks for all your help. I got it working. I just needed to tell the function what variable is being passed.
I haven't worked much with global/public variables but I'm having problems in getting different parts of my program to share data.
I've created a program that searches for people's names on one spreadsheet and assignes the name to a variable. After doing that, it calls a function but I can't pass the name that's stored in the variable to the function. I would like to know how to declare a global/public variable so when it calls a function, it can pass the value.
----------------------------------------------------
Sub DoThis()
Global strName As String
strName = Range("A1").Address
DoThat
End Sub
----------------------------------------------------
Function DoThat()
MsgBox "Name: " & strName
End Function
----------------------------------------------------
I figured this is rather easy but I can't find a solution 
I have a cell with a person's First and Last name but I want to assign only the First name to a variable. How can I accomplish this? Is there a command that reads the value within the cell and stops when it reaches a space or truncates everything after the space?
For example a cell contains: John Doe
I want to assign John to a variable.
I'm trying to copy a chart in Excel and paste it in Word using VBA. The chart has a selected range on a worksheet. I've searched and read that the chart can be copied over as a ".gif" file. Is there any other ways of doing this? I've selected the range and copy the selection but what is the syntax to pasting it to Word?
Thanks for all your help guys. I did the following and I think it works.
strName = InputBox("Enter an Name:")
Range("A1:A20").Select
Cells.Find(What:=strName, After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=False, SearchFormat:=False).Activate
ActiveCell.Offset(rowOffset:=0, columnOffset:=1).Activate
strValue = ActiveCell.Value
Thanks for the replies guys. Let me try to explain what I'm trying to do a little better. I think I made a few mistakes before.
I have a spread sheet with Column A having names and Columns B-P with values. I want to be able to search for a name, find that cell's address (ex. "A22") and from there use "A22" as a reference cell to jump to "B22", "D22" and so forth. Basically, I will assign the data in "B22" - "P22" a variable name so I can later extract that information and send it over to MS Word in a chart. So for example, someone searches for John Doe, all the information for him will be put in a chart and sent to MS Word.
The reason why I didn't prepare a chart in excel with this info and just paste the chart onto MS Word is because I'm not the person entering the data onto the spread sheet. Someone else handles that from time to time. And yes, I'm working from VBA. I've figured out the search part but still need to assign the address to a variable and be able to reference the cell and jump to another column within the same row. Again, thanks for your help guys.
If I search for a name in Excel, how do I get the Range of the cell once the name is found?
For example, I search for for John Doe and it is located in Range("A22"), how do I assign A22 to a variable? How will I later increment A22 to A23 and so forth? Thanks for any help.
Thanks for all your help thomach. Anybody worked with MS Word VBA that can give me some pointers? Any other sites that might provide me with an answer?
thomach, your codes helped me out a bit but I have another questions. If I'm in a MSword macro, how do I open an Excel file and copy a selection over to MSword? Or at least assign it to a variable in MSword macro?
Can anyone give me a simple example of how to read text/data from an excel file in VBA? I only need data from a few selected cells.
Also, what is the newline/carriage return command when writing text to Word in VBA? I know is C++ it's \n or something. Thanks in advance.
