Excel cells function

I have a code to fill an Excel cell with a value using the function provided by Excel.

With CustomerSheet
.Cells(10, 1).Value = "Sum(" & .Cells(irow, icol) & _
":" .Cells(irow + 5, icol) ")"
End With

The above code does not work. I could not use Cells() to refer to a particular cell in the function, but I need to use A1, B1... etc as written in excel's formula bar. However the cells I'm referring for my function is not fixed at design time, so I use variables instead. Hmm anyone know how to manipulate it?

Thanks so much.
~CG

Comments

  • : I have a code to fill an Excel cell with a value using the function provided by Excel.
    :
    : With CustomerSheet
    : .Cells(10, 1).Value = "Sum(" & .Cells(irow, icol) & _
    : ":" .Cells(irow + 5, icol) ")"
    : End With
    :
    : The above code does not work. I could not use Cells() to refer to a particular cell in the function, but I need to use A1, B1... etc as written in excel's formula bar. However the cells I'm referring for my function is not fixed at design time, so I use variables instead. Hmm anyone know how to manipulate it?
    :
    : Thanks so much.
    : ~CG
    :

    Try something like this to put a formula in Cell A1:

    Range("A1").Value = "=" & Range("B1").Value & "+" & Range("C1").Value

    This should put a formula in Cell A1 that adds the values in cells B1 and C1.

    If you wanted to put the same formula in the first 5 rows of column A you could do something like:

    For i = 1 to 5
    Range("A" & i).Value = "=" & Range("B" & i).Value & "+" & Range("C" & i).Value
    Next

  • you could also use ADODB and send an SQL string to let Excel do the work but this way you dont Know when Excel is finished
Sign In or Register to comment.

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Categories