VBA with excel worksheets?

[newbie]

I am new to VBA and am excel user. Can someone tell me:

1 - How to suck a range of excel data cells into vba code?

I just want to put data into some cells and have VBA load that data
into an array and use it then return the result into a specified cell
in a spreadsheet like sheet1 in testfile.xls

I thought i use Worksheets("Sheet1". Range Cells(A1:A6) ....

But this syntax is not right and i am having trouble searching to find
my solution to my problem.

Thanks in advance,

Andrew

 

[Sandy Mann]

I suspect that yhis may be a case of my OE not showing me replies that you
have had because I find it hard to believe that a question like this would
go unanswered for so long on a slow day. You may be better off buying a
book on VBA but to answer your question either use:

Worksheets("Sheet1". Range("A1:A6") or

Worksheets("Sheet1". Range(Cells(1,1),Cells(6,1))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Michael Bednarek]

I am new to VBA and am excel user. Can someone tell me:

1 - How to suck a range of excel data cells into vba code?

I just want to put data into some cells and have VBA load that data
into an array and use it then return the result into a specified cell
in a spreadsheet like sheet1 in testfile.xls

I thought i use Worksheets("Sheet1". Range Cells(A1:A6) ....

But this syntax is not right and i am having trouble searching to find
my solution to my problem.

Try this:
Dim varArray As Variant
varArray = Worksheets("Sheet1").Range("A1:A6")
... manipulate varArray
Worksheets("Sheet1").Range("A1:A6") = varArray

 

[newbie]

Michael,

Thank you for your code segment. I am new to this so my struggle now
is finding code segments and understanding how to create correct code
syntax ....commands to do what i want. I was hunting on the web for
code segments on processing excel data and using VBA but it was hard
to find and search for. For this reason i am asking you questions and
hoping others might read this and throw their ideas in too!

I am looking around now for a good book and some websites for helping
me with VBA and excel?
Could you please give me some advice?

So far i got a book on vba with excel from 1/2 price books but i dont
like it so much. I am going to read it later tonight to see what i can
extract out of it. Most of it isnt so interesting to me and this is
all new to me so its slow going.

I really want to write scientific functions and work with excel data.
Maybe its not the best way? but i want to load excel files with data
where the format is kind of report like....not just a pure data file.
I want to avoid macros. Well after i get done loading data i want to
write out the results to the excel file.

From this section of code seems like i can write out to one cell or
more?

1 = multi cell write excel data to array based on range
Worksheets("Sheet1").Range("A1:A6") = varArray

2 = single cell write to array
Worksheets("Sheet1").Range("F5") = varArray2

How do i write from varArray to a specific cell?
By the way, many thanks for your reply.

Andrew

 

[Michael Bednarek]

Michael,

Thank you for your code segment. I am new to this so my struggle now
is finding code segments and understanding how to create correct code
syntax ....commands to do what i want. I was hunting on the web for
code segments on processing excel data and using VBA but it was hard
to find and search for. For this reason i am asking you questions and
hoping others might read this and throw their ideas in too!

I am looking around now for a good book and some websites for helping
me with VBA and excel?
Could you please give me some advice?

I learnt most of what I know from Excel VBA Help and from Usenet and
from code examples on the Internet. John Walkenbach's books and Chip
Pearson's website are highly regarded around here.

So far i got a book on vba with excel from 1/2 price books but i dont
like it so much. I am going to read it later tonight to see what i can
extract out of it. Most of it isnt so interesting to me and this is
all new to me so its slow going.

I really want to write scientific functions and work with excel data.
Maybe its not the best way? but i want to load excel files with data
where the format is kind of report like....not just a pure data file.
I want to avoid macros. Well after i get done loading data i want to
write out the results to the excel file.

From this section of code seems like i can write out to one cell or
more?

1 = multi cell write excel data to array based on range
Worksheets("Sheet1").Range("A1:A6") = varArray

2 = single cell write to array
Worksheets("Sheet1").Range("F5") = varArray2

How do i write from varArray to a specific cell?
By the way, many thanks for your reply.

I don't quite understand the point of 2; if you want to write a single
value to a cell, this should work:
Worksheets("Sheet1").Range("F5") = (yourVariable)
where (yourVariable) is the variable of whatever you calculated.