find to columns with data

ADRIANA P
ADRIANA P used Ask the Experts™
on
find to columns  

as show here

popio.PNG

as sample i duplicate data to show the find
ooooooooooooooooooooooo.PNG

then if i look for 375 in column J  (FIND)

then the output should be as show here

xxxxxxxxx.PNGto_find_intop.xlsm
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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

Open in new window

to_find_intop.xlsm
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Subodh Tiwari (Neeraj)

Thanks for the faste response best expert

need have an find too in columns  G, H   and   AC AD AE
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
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
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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?
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
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
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
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 )
a1.PNG
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
Show me the expected output if a number is found in column AC or AD or AE.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
here an ouput for column AE  top

a2.PNG
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Subodh Tiwari (Neeraj)

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

Author

Commented:
Subodh Tiwari (Neeraj)

i need correct my mistake the date is very importat to me
a2.PNG
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Subodh Tiwari (Neeraj)

thanks for the great job  

but need an adjusmet

why this  ??  i look for 609 low then

c11111.PNG
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
b111.PNG
Subodh Tiwari (Neeraj)Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015

Commented:
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.
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Subodh Tiwari (Neeraj)

ok i see you rigth !!

is an great job !
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
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

h1111.PNG
Excel & VBA Expert
Most Valuable Expert 2018
Awarded 2015
Commented:
Okay, please check the attached.
to_find_intop-v3.xlsm
ADRIANA PACCOUNTING ASSISTANT

Author

Commented:
Great JOB very  Best Expert !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial