Newbie Question

[Guest]

Hello Everyone. I am a complete newbie to Macro's and would ask 2 things.

1.) Does anyone have some good links to Tutorials on Macros?

2.) I have an excel sheet that has a column of numbers and I would like to
have the sheet show me what row the numbers are in.

A small example:

ROW Col.A Col.B Col.C.
001 01 02 03
002 04 03 02
003 01 02 04
004 02 03 04
005 01 03 04
006 04 03 02

I would like a new sheet created or copied into a preset sheet so it would
look like:

Col.A Col.B Col.C Col.D Col.E Col.F
01 001 003 005
02 001 002 003 004 006
03 001 002 004 005 006
04 002 003 004 005 006

Thank you for any assistance.
David

 

[Guest]

Hi,
Your question raises a lot of other questions. The numbers listed all have
leading zeros, which is not normally the case in Excel, so I guess the first
question is whether these are Text or numbers? Then the next question might
be are the only "numbers" 1-4 or can there be an unlimited number of numbers?

Thanks,

 

[Guest]

They are custom numbers that are formatted in the 00 format. I would say they
can be from number 1 - 99. I would not need it higher then that but I am sure
if it was able then going higher then 99 would be fine.

Thank you for any assistance in this.
David

 

[Guest]

Hi Again,
Will the numbers always be consecutive and with out gaps? What I mean is
that they will alwys be like 1,2,3,4 and not 1,3,4, no gaps?

Thanks,

 

[Guest]

I can understand all the questions for clarification and I appreciate it. My
thoughts are that yes the numbers will be consecutive down the rows like:

1
2
3
4

The columns might have gaps going from 1, 3, 5, 12, 23, 45.. etc. like:
(where Col.B through Col.<whatever letter> would be what rows the number 01,
02, 03, or 04, etc. originally were located.)

Col.A Col.B Col.C Col.D Col.E Col.F
01 001 003 005
02 001 002 003 004 006
03 001 002 004 005 006
04 002 003 004 005 006

Thank you
David

 

[Guest]

Hi,
Try this. It assumes numbers in range 1- 99 and collates the rows
for each number on Sheet2. If a number is not found in the original range,
the corresponding row in Sheet2 is blank.


Sub FindRows()

Dim rng As Range, rngA As Range, c As Range
Dim ws1 As Worksheet, ws2 As Worksheet
Dim i As Integer, idx As Long

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

ws1.Select
' Change this range as required
Set rng = ws1.Range("A1" & Cells(Rows.Count, "A").End(xlUp).Row)

For i = 1 To 99 ' Set numbers 1 to 99 in col A of Sheet2
ws2.Cells(i, 1) = i
Next i

For Each c In rng ' Loop through each cell in selected range
idx = c.Value ' Row index in Sheet 2
With ws2
Set rngA = .Range("A" & Trim(Str(idx)) & ":IV" & Trim(Str(idx)))
' COUNTA is used to determine last used column for selected row IDX
.Cells(idx, Application.CountA(rngA) + 1) = c.Row ' Add to next column
End With
Next c

End Sub

HTH

 

[Guest]

That was exactly what I needed. Did the trick perfectly.
Thank you.

David