• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Finding string in an excel

macro_learning

New Member
Hi,

Can anyone please help me with below code, which should return a cell address which contains word " rate".

range1 = Cells.Find(what:="rate")

Regards
 
Hi ,

Try this :
Code:
Public Sub Get_Found_Address()
           Set Range1 = Cells.Find(what:="rate")
           If Range1 Is Nothing Then
              MsgBox "Text Not Found !", vbInformation
           Else
              MsgBox Range1.Address
           End If
End Sub
Narayan
 
What mistake am I doing here :(,

------------------

Sub get_ratefromothr_book()

Dim range1 As Range
Dim payment As Long
Dim x As Range
Dim wb As Workbook

Set wb = Workbooks.Open("E:\Macro\jumping to perticular cell.xlsx", True, True)

Set range1 = Cells.Find(what:="rate") ' rate word is there in column A

x = range1.Offset(0, 1).Value ' should assign x a value in a cell next to rate word, means from column B)

payment = 15 * (1 + x)
MsgBox payment
End Sub

----
Regards
 
Hi ,

You have declared x as a variable of type Range. Declare it correctly as a variable of type Integer , Long or Double , depending on what kind of value will be in the column B cell.

Narayan
 
Hi ,

This is only possible if the word rate does not exist in the worksheet , or the corresponding cell in column B does not contain a number.

Narayan
 
Hi ,

It will work for all numbers , unless the number exceeds the limits of the type.

For instance the Integer type has a limit , which I am not sure about , but let us assume it is 32767. Now , if the cell contains the number 40000 , since this exceeds the limit of the type Integer , Excel will generate an error.

However , suppose you have declared the variable as type Integer or Long ; both of these types can accept only whole numbers ; if you now assign a decimal value to this variable , Excel will truncate the number.

For instance , if you assign the value 0.4 to a variable of type Integer or Long , the value actually assigned to the variable will be 0.

But no error will be generated.

Narayan
 
Hi,
i want to search values in a sheet1 to sheet2.where ever matching found then copy entire row paste in another sheet3.

Please post answer for this one
 
Back
Top