Loop through Range

[MUKESHY12390]

Hi, Frnds

m trying to do Loop through Range
I have workbook and it has two sheet (sheet1 and sheet2)
sheets("Sheet1").range(A1:K30") hold the Doc_ID (eg. 78002)
what i want to do........if Range(A1:K30) = Inputbox("Enter Your Doc_Id Number") Then Copy that Cell Only and paste it to in Sheets("Sheet2") -Column A


Pls help me..................!

 

[NdNoviceHlp]

HTH. Dave

Sub LoopRange()
Dim Answer As Integer, Rng As Range, C As Range, Temp
Temp = InputBox("Enter Your Doc_Id Number")
If Temp = vbNullString Then
MsgBox "No input entered!"
Exit Sub
Else
Answer = Temp
End If
Set Rng = Sheets("Sheet1").Range("A1:K30")
For Each C In Rng
If C.Value = Answer Then
With Sheets("Sheet2")
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
   .Range("A" & lastrow + 1) = Answer
End With
Exit Sub
End If
Next C
MsgBox "No Find " & Answer
End Sub

 

[JakeTheSnake3.0]

Whenever possible try and use Excel's built-in functions for doing things like this.

Dim usrSearch$, srchResult As Range
usrSearch = InputBox("Enter Your Doc_Id Number")
Set srchResult = Sheets("Sheet1").[[COLOR=#333333]A1:K30[/COLOR]].Find(Trim(usrSearch), Sheets("Sheet1").[A1], xlValues, xlWhole)
If Not srchResult Is Nothing Then
    With Sheets("Sheet2")
         If IsEmpty(.[A1]) Then
             .[A1].Value = usrSearch
         Else
             .Columns(1).End(xlUp).Offset(1, 0).Value = usrSearch
         End If
    End With
End If

 

[MUKESHY12390]

Whenever possible try and use Excel's built-in functions for doing things like this.

Dim usrSearch$, srchResult As Range
usrSearch = InputBox("Enter Your Doc_Id Number")
Set srchResult = Sheets("Sheet1").[[COLOR=#333333]A1:K30[/COLOR]].Find(Trim(usrSearch), Sheets("Sheet1").[A1], xlValues, xlWhole)
If Not srchResult Is Nothing Then
    With Sheets("Sheet2")
         If IsEmpty(.[A1]) Then
             .[A1].Value = usrSearch
         Else
             .Columns(1).End(xlUp).Offset(1, 0).Value = usrSearch
         End If
    End With
End If

ABOVE CODE COPY ONLY ONE MACH ,INSTEAD OF 26 MATCH .................!!! need more help.!!

 

[MUKESHY12390]

HTH. Dave

Sub LoopRange()
Dim Answer As Integer, Rng As Range, C As Range, Temp
Temp = InputBox("Enter Your Doc_Id Number")
If Temp = vbNullString Then
MsgBox "No input entered!"
Exit Sub
Else
Answer = Temp
End If
Set Rng = Sheets("Sheet1").Range("A1:K30")
For Each C In Rng
If C.Value = Answer Then
With Sheets("Sheet2")
    lastrow = .Range("A" & .Rows.Count).End(xlUp).Row
   .Range("A" & lastrow + 1) = Answer
End With
Exit Sub
End If
Next C
MsgBox "No Find " & Answer
End Sub

ABOVE CODE COPY ONLY ONE MACH ,INSTEAD OF 26 MATCH .................!!! need more help.!!

 

[JakeTheSnake3.0]

By your own words

> Copy that Cell Only

If you wanted all instances of the search criteria copied you should have said so. Even that being said you have enough to go on here to research online a littler further without having to get us to do the whole thing for you.

http://msdn.microsoft.com/en-us/library/office/aa195730(v=office.11).aspx

Also ozgrid.com is a great resource for learning Excel VBA.

 

[MUKESHY12390]

---------if Range(A1:K30) = Inputbox("Enter Your Doc_Id Number") Then Copy that Cell Only and paste it to in

I was just trying to say , code should copy All Matches in Range("A1:K30")
But no Problem........I just got the Solution...!

Sub loop_through_Range()
Dim rng As Range
Set rng = Sheets("sheet1").Range("A1:E20").Cells
ms = Application.InputBox("Enter Doc_ID Number", Type:=1)
For Each Cell In rng
    If Cell.Value = ms Then
      Cell.Copy
      Sheets("sheet2").Activate
        Range("E500").End(xlUp).Offset(1, 0).Select
           ActiveSheet.Paste
      End If
 Next Cell
End Sub

Thanks.. For Reply...!!!