Using VBA select cell with word

[ajith]

How to select using VBA the cell with the result as "Total" in the range C4 to C100.

 

[footoo]

[C4:C100].Find("Total").Select

 

[My Aswer Is This]

Try this:

Sub My_Find()
'Modified 7/8/18 5:45 AM EDT
Dim SearchString As String
Dim SearchRange As Range
SearchString = "Total"
Set SearchRange = Range("C4:C100").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
    If SearchRange Is Nothing Then MsgBox "The Value" & SearchString & vbNewLine & "Not found": Exit Sub
SearchRange.Select
End Sub

 

[ajith]

Sir,
Thank you. Great.

 

[ajith]

Sir,
In continuation to my query. If I want to paste the following code starting from that cell which contains "Total", how should I proceed. Thanks in advance.

Sub CopyState2()'
' CopyState2 Macro
'


'
    Sheets("Arr Statement").Select
    Range("C5:L89").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Draft").Select
    Range("C89").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.ClearContents
    Sheets("Arr Statement").Select
    Selection.Copy
    Sheets("Draft").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
End Sub

Thank you.

 

[ajith]

Sir,
I could do that as follows,

Sub CopyState3()
'
' CopyState2 Macro
'


'
    Sheets("Arr Statement").Select
    Range("C5:L89").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Draft").Select
    Dim SearchString As String
Dim SearchRange As Range
SearchString = "Total"
Set SearchRange = Range("C4:C1046").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
    If SearchRange Is Nothing Then MsgBox "The Value" & SearchString & vbNewLine & "Not found": Exit Sub
SearchRange.Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.ClearContents
    Sheets("Arr Statement").Select
    Selection.Copy
    Sheets("Draft").Select
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
End Sub

I had to do PasteSpecial twice as even after selecting "Value and number formatting" under paste special the formulas are seen. I am using MS office Professional Plus 2016.

Thank you,

 

[mrhstn]

Maybe something like this...

Sub CopyState3()
Dim SearchString As String
Dim SearchRange As Range
SearchString = "Total"
Set SearchRange = Sheets("Draft").Range("C4:C1046").Find(SearchString, LookIn:=xlValues, lookat:=xlWhole)
    If SearchRange Is Nothing Then MsgBox "The Value" & SearchString & vbNewLine & "Not found": Exit Sub
SearchRange.Resize(84, 8).Value = Sheets("Arr Statement").Range("C5:L89").Value
End Sub

 

[My Aswer Is This]

It would have been great from the start if you had explained in full what your attempting to do.

Like why are you searching for the word Total
Is this the value in the last cell in a particular column with data?
If so there are other ways we would do this.

 

[ajith]

Sorry sir. I understand that. But If I post many things it will be confusing for the reader. Moreover I don't know to upload an excel sheet along with my query. Earlier someone informed me that it is given in this forum but it is not self explanatory.