Overflow error VBA

[asparagus]

Dear Master
I already try to solve this problem but is not clear.
When i search date into input box always show error overflow message.
I try to change as integer became Long but still not clear
this my code

Sub SearchForString()

  Dim LSearchRow As Integer
  Dim LCopyToRow As Integer
  Dim LSearchValue As String
  
  On Error GoTo Err_Execute
  
  LSearchValue = Application.InputBox("Please enter a value to search for.", "Enter Date", Format(Now(), "dd-mmm-yy"))
  'Start search in row 4
  LSearchRow = 7
  
  'Start copying data to row 2 in Sheet2 (row counter variable)
  LCopyToRow = 2
  
  While Len(Range("A" & CStr(LSearchRow)).Value) >= 0
  
  'If value in column E = LSearchValue, copy entire row to Sheet2
  If Range("B" & CStr(LSearchRow)).Value = LSearchValue Then
  
  'Select row in Sheet1 to copy
  Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
  Selection.Copy
  
  'Paste row into Sheet2 in next row
  Sheets("Sheet2").Select
  Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
  ActiveSheet.Paste
  
  'Move counter to next row
  LCopyToRow = LCopyToRow + 1
  
  'Go back to Sheet1 to continue searching
  Sheets("Monitoring List CKD F-Series").Select
  'MsgBox "Data Found" & Error
  End If
  
  LSearchRow = LSearchRow + 1
  
  Wend
  'Position on cell A3
  Application.CutCopyMode = False
  Range("A3").Select
  Exit Sub
  
Err_Execute:
  MsgBox "An error occurred." & Error
  
End Sub

anyone can help me
Regards,
AsparAgus

 

[NARAYANK991]

Hi ,

If you can upload your workbook , it will make it easier for others to help.

Please remember that when ever you face a problem in code , please post the workbook , instead of posting only the code.

Narayan

 

[asparagus]

Hi Narayan,

Okay this is my workbook, thanks before

Regards,
AsparAgus

 

[Hui]

Asparagus

Why is there two posts the same ?

I am going to delete the other post ok

 

[Hui]

Can you please explain in simple words what your trying to do ?

 

[NARAYANK991]

Hi ,

There are two issues in your code :

1. It is searching only in column B ; can you confirm whether this is what you want ? Or do you want to search in other columns also ? If so , which are these columns ?

2. Your While loop will continue executing till it runs out of rows ! Which is why declaring the variable as Long will also not help. You need to redo this logic ; first find out which is the last row with data , and then loop from the start row ( row 7 in your case ) till this derived last row.

Narayan

 

[asparagus]

Hi Narayan & Hui

I want search using input box with date "28-Nov-14" just from column B.
My Problem in this code

While Len(Range("A" & CStr(LSearchRow)).Value) >= 0

when I change ">=0" became ">0" it success to copy value in sheet 2, but just 1 value if value more than 2.
and if i using ">=0" show overflow error.

can you give me example code narayan to looping code what you mean.

Regards,
AsparAgus

 

[NARAYANK991]

Hi ,

See your file now.

Narayan