How do I get VBA to read and store values in worksheet cells?

How do I get VBA to read and store values in worksheet cells?

Post by Built Environment Computing Studio » Fri, 11 Aug 1995 04:00:00



Hi there,

I am new to VBA although not new to VB, nevertheless I have found VBA to
be an absolute nightmare.

I am trying to acheive something very simple that should be a very basic
task in a spreadsheet macro language:

     varable1 = cell(row,col)
     .
     .

     .
     .
     cell(row,col) = modified_variable1

I have found the Excel manuals next to useless, and the on-line help
gives me volumes of information but not what I need to know.

I have tried things like:

   variable1 = worksheets("Sheetname").cells(row,col).value
   variable1$ = worksheets("Sheetname").cells(row,col).text
   variable1 = worksheet(Sheetnumber).cells(row,col).value
   variable1 = cells(row,col).value

and many other variations on the same theme including things like
activesheet instead of worksheet etc. Nothing seems to work. I just keep
getting null values back or syntax errors.

Can anyone help?

I have also seen other letters in this newsgroup about a similar problems
so I see that I am not the only one. To be able to acheive the above
is surely a very basic function for a macro language associated with a
spreadsheet and should not be so difficult to find a solution to. But
then it IS made by Microsoft....

So what is Microsoft playing at? If they are serious about making this an
office application development language then perhaps they should do
something about simplifying it and providing some decent manuals.

--
         _______     _____
   ##===(__   __)<==/  ___)<============= Network Technician <=======##
   ||      | |      | |             School of the Built Environment  ||
   ||     _| |ohn   | |___hajecki       De Montfort University       ||

 
 
 

How do I get VBA to read and store values in worksheet cells?

Post by Dave Barte » Fri, 11 Aug 1995 04:00:00


Quote:>I am trying to acheive something very simple that should be a very basic
>task in a spreadsheet macro language:

The methods you are looking for are "Sheets()" and "Cells()"

As an example, to copy the value of a cell from A1 to A2 using an
intermediate variable run the following subroutine:

Sub Test1()
    var1 = Cells(1, 1).Value
    Cells(1, 2).Value = var1
End Sub

The previous subroutine applies only to the active worksheet in the
active workbook, you could also use the following to apply it to a
specific worksheet:

    var1 = Sheets("Sheet1").Cells(1,1).Value
    Sheets("Sheet1").Cells(1,1).Value = var1

For more info, look at the MS help files on Sheets or Cells.
Dave Bartels
Research Engineer, Catalytic Cracking
Amoco Petroleum Products

 
 
 

How do I get VBA to read and store values in worksheet cells?

Post by Steve Goi » Sat, 12 Aug 1995 04:00:00



Quote:>Hi there,
>I am new to VBA although not new to VB, nevertheless I have found VBA to
>be an absolute nightmare.
>I am trying to acheive something very simple that should be a very basic
>task in a spreadsheet macro language:
>     varable1 = cell(row,col)
>     cell(row,col) = modified_variable1
>I have found the Excel manuals next to useless, and the on-line help
>gives me volumes of information but not what I need to know.
>I have tried things like:
>   variable1 = worksheets("Sheetname").cells(row,col).value
>   variable1$ = worksheets("Sheetname").cells(row,col).text
>   variable1 = worksheet(Sheetnumber).cells(row,col).value
>   variable1 = cells(row,col).value
>Can anyone help?

I found that some of your code works in Windows.

        variable1 = Cells(row,col)
if row is 3 and col  is 4, the variable1 gets the default property
(value) of range D3.
        Cells(row,col)=variable2
works fine too.

The help information available, once you've started using it, is
fantastic.  With the insertion around a word you need info on,
pressing F1 will bring up the needed information, with good examples.
Particularly helpful is the colorcoding and capitalization.  VBA will
change recognized objects, properties, methods to proper case.

 
 
 

How do I get VBA to read and store values in worksheet cells?

Post by Tony P » Mon, 14 Aug 1995 04:00:00


[Posted in comp.apps.spreadsheets]


1995 11:48:57 GMT:

:Hi there,

:I am new to VBA although not new to VB, nevertheless I have found VBA to
:be an absolute nightmare.

:I am trying to acheive something very simple that should be a very basic
:task in a spreadsheet macro language:

:     varable1 = cell(row,col)
:     .
:     .

:     .
:     .
:     cell(row,col) = modified_variable1

:I have found the Excel manuals next to useless, and the on-line help
:gives me volumes of information but not what I need to know.

:I have tried things like:

:   variable1 = worksheets("Sheetname").cells(row,col).value
:   variable1$ = worksheets("Sheetname").cells(row,col).text
:   variable1 = worksheet(Sheetnumber).cells(row,col).value
:   variable1 = cells(row,col).value

:and many other variations on the same theme including things like
:activesheet instead of worksheet etc. Nothing seems to work. I just keep
:getting null values back or syntax errors.

:Can anyone help?

I think that you need to use the Range method.  Give this a try, it's
very simple but can be easily elaborated  <G>

Sub Try_This()
    Dim Var1
    Let Var1 = Range("C2")
    Range("A9") = Var1 * 6
End Sub

Just put a value in cell C2 and run it.  You can of course declare the
value of Var1 within the Sub should you wish.

Hope it helps a little.

Tony


FidoNet: 2:254/220 or 2:254/151

 
 
 

1. Using worksheet function "TREND" in VBA and assigning returned values to VBA variables?

Bonjour,

I have the following problem:
In my VBA code, I would like to use the worksheet function TREND in my
VBA code and assign whatever the function returns to a scalar or an
array VBA variable.

When I use a worksheet function such as "Application.MIN" that returns a
scalar, it works as expected. It's only when I try to use functions such
as "Application.TREND" or "Application.LINEST" that return several
values at once that I have problems.

Below is the code that refuses to work:
'--------------------------------------------------
Function myTrend()  ' NOT WORKING
        Dim returnedValues As Variant
        Dim myArray as variant
        Dim myArg as String
        myArg = "A1:D1,,5"       'hard-coded argument for testing purposes

        returnedValues = Application.Trend(myArg)

        myArray = Array(returnedValues)  'here I am trying to coerce
                                                ' the returnedValues into an array

        myTrend = myArray(1)   'trying to return the first element of
                           'the array. I get #NAME? as a result...

 End Function
'---------------------------------------------------
Now, if in a worksheet, I put the formula "=TREND(A1:D1,,5)" in a cell,
I get the correct answer. Why can't I retrieve that same return value
using VBA???

Thank you for any help you can offer, I have been breaking my head at
this problem for several days now.

Carlos Tremblay
tremblac/at/fonorola/dot/com
Systems Analyst
Fonorola Inc., Montreal, Quebec

2. Domino R5 behind MS Proxy (+OP4)

3. Using a Cell Value in Another Spread/WorkSheet

4. flattening C++ objects

5. vba short hand for copy between worksheets (value)

6. Device Driver Issue

7. Changing Values in Excel Worksheet via a Chart Object and VBA

8. All NTFS partitions dirty

9. Prevent Copying Worksheet Cells to New Worksheet

10. setting one worksheet cell range reference to another worksheet

11. AutoComplete returns valid cell reference to VBA but range.value is empty?

12. Excel 5.0 VBA: Getting row/column of active cell

13. Excel VBA and cell value?