find to columns with data

Please give this a try...
In the attached, click the button called "FIND" on Sheet1 to run the code.

Sub FindMidLowTopGHJ()
Dim wsData As Worksheet, wsOut As Worksheet
Dim lr As Long, c As Long, lc As Long, cnt As Long
Dim Num As String
Dim Rng As Range, Cell As Range
Dim FirstAddress As String

Application.ScreenUpdating = False
Set wsData = Sheets("Sheet1")
Set wsOut = Sheets("DTAOUT")
wsOut.Cells.Clear
lr = wsData.Cells(Rows.Count, "J").End(xlUp).Row
Set Rng = wsData.Range("J4:J" & lr)
Num = InputBox("Please input the Number to be find")
c = 2
With Rng
    Set Cell = .Find(Num, lookat:=xlWhole)
    If Not Cell Is Nothing Then
        FirstAddress = Cell.Address
        Do
            cnt = cnt + 1
            wsOut.Cells(1, c) = Num
            wsOut.Cells(2, c) = "OCCURRENCE " & cnt
            Cell.Offset(0, -3).Resize(10, 7).Copy wsOut.Cells(4, c)
            Set Cell = .FindNext(Cell)
            c = c + 8
        Loop While Not Cell Is Nothing And FirstAddress <> Cell.Address
    End If
End With

lc = wsOut.Cells(4, Columns.Count).End(xlToLeft).Column
If lc > 1 Then
    For c = 2 To lc Step 8
        wsOut.Cells(1, c).Resize(2, 7).Interior.Color = RGB(255, 192, 0)
        wsOut.Cells(1, c).Resize(1, 7).Merge
        wsOut.Cells(1, c).HorizontalAlignment = xlCenter
        wsOut.Cells(2, c).Resize(1, 7).Merge
        wsOut.Cells(2, c).HorizontalAlignment = xlCenter
    Next c
End If
wsOut.Activate
Application.ScreenUpdating = True
End Sub

Select allOpen in new window

to_find_intop.xlsm

Subodh Tiwari (Neeraj)

Thanks for the faste response best expert

need have an find too in columns  G, H   and   AC AD AE

Right now the code in the file I uploaded contains a code which will find the number from column J and place the matching data on DTAOUT Sheet. Is it working as desired?

Do you mean you also want another code to search the number in column AE and place the matching data on DTAOUT sheet?
If so, what would be the expected output in that case? Can you upload a sample file with the desired output mocked up manually as you did in your last sample file?

Subodh Tiwari (Neeraj)
Right now the code in the file I uploaded contains a code which will find the number from column J and place the matching data on DTAOUT Sheet. Is it working as desired?

yes!!

Do you mean you also want another code to search the number in column AE and place the matching data on DTAOUT sheet?

need the same look up  for  columns  G , H   and   AC   ,   AD   , AE   too

If so, what would be the expected output in that case? Can you upload a sample file with the desired output mocked up manually as you did in your last sample file?


i expect the same as the column J did  but
the same is needed  for columns  G , H   and   AC   ,   AD   , AE   too

Please show me an example of the desired output in the file itself.
Also do you want a separate button for columns AC, AD and AE?

Subodh Tiwari (Neeraj)

Also do you want a separate button for columns AC, AD and AE?

can be separate or can be  just one  

but need  look for  for  G , H   and   AC   ,   AD   , AE   too not just column J

here the outpu for column H sample  looking for number 609

(it looks like the same column J because i just duplicate the values but they will be diferents when working with the solution )

Show me the expected output if a number is found in column AC or AD or AE.

here an ouput for column AE  top

Subodh Tiwari (Neeraj)

the word TOP  LOW Or MID will help me near to the selected numebrs as show above

Subodh Tiwari (Neeraj)

i need correct my mistake the date is very importat to me

Please test the attached. You may click the button called "FIND" to run the code and search for a number in columns G, H, J, AC, AD and AE.
to_find_intop-v2.xlsm

Subodh Tiwari (Neeraj)

thanks for the great job  

but need an adjusmet

why this  ??  i look for 609 low then

That's because 609 is found in multiple columns not in just one column. And how does the code know which column you are searching the number in? Look at the Row1 and it has different headers for each block.

Subodh Tiwari (Neeraj)

ok i see you rigth !!

is an great job !

Subodh Tiwari (Neeraj)

can be this done Please !

i need call columns  G H J  as   FULLWEEK

then columns   AC   AD    AE  as MID WEEK

Great JOB very  Best Expert !