find to columns with data

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
ADRIANA PACCOUNTING ASSISTANTAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Subodh Tiwari (Neeraj)

Thanks for the faste response best expert

need have an find too in columns  G, H   and   AC AD AE
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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?
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ADRIANA PACCOUNTING ASSISTANTAuthor 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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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?
0
ADRIANA PACCOUNTING ASSISTANTAuthor 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
0
ADRIANA PACCOUNTING ASSISTANTAuthor 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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Show me the expected output if a number is found in column AC or AD or AE.
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
here an ouput for column AE  top

a2.PNG
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Subodh Tiwari (Neeraj)

the word TOP  LOW Or MID will help me near to the selected numebrs as show above
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Subodh Tiwari (Neeraj)

i need correct my mistake the date is very importat to me
a2.PNG
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Subodh Tiwari (Neeraj)

thanks for the great job  

but need an adjusmet

why this  ??  i look for 609 low then

c11111.PNG
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
b111.PNG
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
0
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Subodh Tiwari (Neeraj)

ok i see you rigth !!

is an great job !
0
ADRIANA PACCOUNTING ASSISTANTAuthor 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
0
Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
Okay, please check the attached.
to_find_intop-v3.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ADRIANA PACCOUNTING ASSISTANTAuthor Commented:
Great JOB very  Best Expert !
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Office

From novice to tech pro — start learning today.