Results 1 to 13 of 13
  1. #1
    Join Date
    Feb 2007
    Location
    The Farm
    Posts
    35

    Unanswered: Cells() function

    I am trying to set the forumla of a cell to particular row and column with
    Code:
             Cells(SunRow, 16).Formula = "=P" '+ Str(Cells(Rowcounter, 16))
    but it complains about "type mismatch". I have narrowed in down to the Str(Cells(Rowcounter, 16)) statement but I am stuck at that point.

    I can not find the Cells() function described in help. Can someone help me along or tell me how to search and find the documentation for Cells()?

    Thanks
    Bartron

    Liv'n down on the cube farm. Left, then another left, then right.

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    "=P" '+ Str(Cells(Rowcounter, 16))
    Extra apostrophe?
    And if you are trying to concatenate then you use an ampersand (&) not a plus sign

    HTH
    George
    Home | Blog

  3. #3
    Join Date
    Feb 2007
    Location
    The Farm
    Posts
    35
    Sorry about the apostrophe. I had commented out that protion of the line when I was running a test. I forgot to remove it before presenting it to you.

    You are correct that it should be & instead of + for concatination. I am also working in SQL Server where the + is the concatination operator.

    Now I am getting a "Run-time error '1001': Application-defined or object-defined error". The current incaration of my code is
    Code:
            
            Select Case currowcell.Value
                Case "Sun"
                    Cells(SunRow, 16).Formula = "=P" & Str(Cells(Rowcounter, 16))
                
                Case "Tue"
                    Cells(SunRow, 18).Formula = "=R" & Str(Cells(Rowcounter, 18))
            End Select
    The lines that are indicated as being in error are where I am trying to assign the formula.

    Just to recap, what I am trying to accomplish is that as I run through the rows of my spreadsheet I want to build a formula that will assign the value my total rows to other cells. Because the rows where I want the totals to appear change every month I am trying to do it dynamically. If someone has a better hammer to accomplish what I am attempting I am all ears.

    Thanks
    Bartron

    Liv'n down on the cube farm. Left, then another left, then right.

  4. #4
    Join Date
    Oct 2003
    Posts
    1,091
    How have you defined SunRow and Rowcounter?
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you were to do this on your spreadsheet (not through VBA); what would you be typing into the cell to get your required result?
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2007
    Location
    The Farm
    Posts
    35
    Shades: No I haven't nor do I think I can. Don't variables automagically delcare themselves when first referenced?

    GeorgeV: I would type something like "=P16"
    Bartron

    Liv'n down on the cube farm. Left, then another left, then right.

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    I think what shades meant is have you assigned a value ti sunrow and rowcounter !

    Variable do "self declare" if you are NOT using "Option Explicit" at the top of your module (this is highly recommended you use Option Explicit).

    BTW what is "currowcell" It appears to be an object of some sort with .Value returning a string !?

    On the basis that all your variable are set correctly then try this

    Code:
        Select Case currowcell.Value
            Case "sun"
                Cells(sunrow, 16).Formula = "=P" & Cells(Rowcounter, 16)
            
            Case "Tue"
                Cells(sunrow, 18).Formula = "=R" & Cells(Rowcounter, 18)
        End Select

    MTB

  8. #8
    Join Date
    Oct 2003
    Posts
    1,091
    Quote Originally Posted by MikeTheBike
    Hi

    I think what shades meant is have you assigned a value ti sunrow and rowcounter !

    Variable do "self declare" if you are NOT using "Option Explicit" at the top of your module (this is highly recommended you use Option Explicit).

    BTW what is "currowcell" It appears to be an object of some sort with .Value returning a string !?
    Howdy, Mike. Yes, that was what I was thinking. Without declaring them, they could be almost anything and even change type. Not a good situation.

    When I first encountering Option Explicit I was frustrated (a few hours), until I began to see the practical advantage of declaring all variables. It didn't take long (even for an old codger like me) to discover that by changing that setting in the VBA Options helps considerably, forcing good coding discipline.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  9. #9
    Join Date
    Feb 2007
    Location
    The Farm
    Posts
    35
    GentlePersons (just being politically correct :-), I am fairly certain that you are both Gentlemen but....)

    here is the total routine I am trying to get to run
    Code:
    Sub tracktest()
    
        SunRow = 0
    
        For Rowcounter = 5 To 35
        
            Set currowcell = Worksheets("Current").Cells(Rowcounter, 1)
            If currowcell.Value = "Sun" Then
                ' Keep track of hours across the page
                ' for easy transfer to ProjectWeb
                SunRow = Rowcounter
                             
            End If
            
            If Rowcounter = 5 And SunRow = "" Then
                SunRow = Rowcounter
                
            End If
            
            Select Case currowcell.Value
                Case "Sun"
                    Cells(SunRow, 16).Formula = "=P" & Cells(Rowcounter, 16)
                
                Case "Tue"
                    Cells(SunRow, 18).Formula = "=R" & Cells(Rowcounter, 18)
            
            End Select
                    
        Next Rowcounter
    
    End Sub
    I think this iteration is where I started from originally. I am still getting the 1004 error however.

    You guys are great. Thanks for all the help. In case this will help visualize a solution or a better solution, what I am try to do is take data stored in several rows and display it in 1 row in consecutive columns. e.g.

    a
    b
    c
    d
    becomes
    a b c d
    Bartron

    Liv'n down on the cube farm. Left, then another left, then right.

  10. #10
    Join Date
    Oct 2003
    Posts
    1,091
    Howdy. Couple of points.

    1. Again, declaring your variables will help. SunRow seems to be used two different ways in your code. Add the following at the top of your code

    Code:
    Dim currowcell as Range
    Dim Rowcounter as Long
    Dim SunRow as Long
    2. After you do that, then what line does the code get stuck on? To go line-by-line, use the F8 key and step through the code. It will indicate which line is bad.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  11. #11
    Join Date
    Dec 2004
    Posts
    28
    Hi, why don't you try something like this:
    Code:
    Option Explicit
    Sub test()
    Dim a As Integer
    a = 0
    With Workbooks("test.xls").Sheets("Sheet1")
    While .Cells(a + 1, 1) <> ""
     .Cells(1, a + 2) = .Cells(a + 1, 1)
    a = a + 1
    Wend
    End With
    End Sub
    It works for transferring data from a column to a row.

  12. #12
    Join Date
    Oct 2003
    Posts
    1,091
    Regarding this solution, you should change the line

    Code:
    Dim a As Integer
    to

    Code:
    Dim a As Long
    for two reasons: 1) Excel will convert it to Long anyway, 2) Integer only allows up to ~32K rows. What happens if you need it to work on row 43,239? The above code would fail/give incorrect result. By changing to Long, you avoid the problem.
    old, slow, and confused
    but at least I'm inconsistent!

    Rich
    (retired Excel 2003 user, 3/28/2008)

    How to ask a question on forums

  13. #13
    Join Date
    Dec 2004
    Posts
    28
    Hi shades, I didn't take that in account because you only have 256 columns. But you have a point there.
    Cheers

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •