[VBA] Get the content of each value of a line

[Nemroth]

Hello to everyone.

I've got an Excel sheet which is organized in a sheet of data. On the
first line, there are the titles in each column.
The first line contains, for each line, a unique code.

I want to get, in VBA, the content of each cell of a line corresponding
to a code and to get these values in a table of variables, or, better,
in a structure.

Can you show me how to do that, please ?

Thanks in advance.

Example of a line :

A B C D
Code Firm Adress 1 City
GDPZ Something 10 Freeware St New York

 

[AB]

Perhaps a bit more light on this:
'> I want to get, in VBA, the content of each cell of a line
corresponding

to a code and to get these values in a table of variables, or, better,
in a structure.

'
wouldn't hurt - i mean, there might be a few solutions depending on
what do you mean by 'table of variables' or 'in a structure'. For one,
you can just pull it straight into a dynamic array or you need to loop
through the ranges one by one and store each separately in a variable
or you can use collection/dictionary. The choice will depend on the
specifics. So, if you provide a bit more clarity on the paragraph i
quoted, i'm sure people here will come up with a great solution.

 

[Nemroth]

Thanks for your answer
Sorry if I wasn't explicit, I'm not english/american native language.
By 'table of variables' I mean a dynamic array and by 'Structure' I mean
a collection like :

Dim Code As String
Dim Firm As String
Dim Adress1 As String
Dim City As String

and then someting like :

Struct(i).Firm = "Something"
Struct(i).Adress1 = "10 Freeware St"

and so on...

I want to get the content of each cell of the line corresponding to the
code (which is unique). I think about collection as each value can be
off a different type (String, Currency, ...)

AB a écrit :

 

[AB]

Not considering all the specifics and making it dynamic, would
something like this do the trick (i mean in general - would structure
like this ('type') be something you're after):

Public Type Structure
Code As String
Firm As String
Address1 As String
City As String
End Type


Sub FillMyStructure()
Dim mStrc As Structure

With mStrc
.Code = Cells(2, 1)
.Firm = Cells(2, 2)
.Address1 = Cells(2, 3)
.City = Cells(2, 4)
End With

End Sub

Paste the above code in a new standard VBA module and try it out. Try
stepping it throughy with F8. You can also try reading some help un
using UDT (User Defined Type) whereby a bunch of variables are
combined into one type - as per above there is a type 'Structure' that
holds 'Code', 'Address1', 'Firm', 'City'.

A.

 

[Nemroth]

Thanks almost AB. Its near of what I need.
The only thing I have to know is how to find the value (in the example)
of the code GDPZ, which is in the second line in the example but that
can be on any line. the A colomn should be like that :

A
Code
AZGT
HJKD
..
..
GDPZ
..
HPIR

and so on

So in ".Code = Cells(2, 1)" I must replace "2" by the line number where
the code is, which can be any line number. How to obtain it ?

Thanks for your answers.

AB a écrit :

 

[Nemroth]

Sorry : Thanks a lot AB (and not almost !!!)

Nemroth a écrit :

 

[AB]

I'm glad to help.

Look at/try out this code:

Public Type Structure
Code As String
Firm As String
Address1 As String
City As String
End Type

Public Const myCode As String = "GDPZ"


Sub FillMyStructure()

Dim mStrc As Structure
Dim myFoundCell As Range
Set myFoundCell = Columns("A").Find(what:=myCode, lookat:=xlWhole,
MatchCase:=False)

If Not myFoundCell Is Nothing Then
With mStrc
.Code = myFoundCell.Offset(0, 0)
.Firm = myFoundCell.Offset(0, 1)
.Address1 = myFoundCell.Offset(0, 2)
.City = Cells(myFoundCell.Row, 4) 'I use different
structure here purely for ilustration purposses - _
you can replace this with "myFoundCell.Offset
(0, 3)" and you'd get the same result.
End With
Else
MsgBox "Could not find code: '" & myCode & "' on this
sheet!!!", vbCritical
End If

Stop 'This is here for you to see that the variable holds all the
values you need. in the final code remove this line

End Sub

Now the code will find the row dinamically BUT what's still not
dinamic is the code value to search for, i.e., as you can see i've
hardcoded this bit:
Public Const myCode As String = "GDPZ"
This means that unless you make that dynamic, the macro will always
look for "GDPZ". If you need help with this too, post back the logic
how/wherefrom you obtain the code values to search for and will try
figure somthing out.

 

[Nemroth]

Thanks AB. It's perfect for me. I tried it and it's a very good base for
me to make exactly what I want. Thanks again.

AB a écrit :

 

[AB]

No probs.
Thanks for the feedback.

Thanks AB. It's perfect for me. I tried it and it's a very good base for
me to make exactly what I want. Thanks again.

AB a écrit :














- Show quoted text -