using for-next index in range variable

  • Thread starter Thread starter cporter
  • Start date Start date
C

cporter

MSDN seems to say that range values must be written in the A1 format. I
need to compare a value in column D (sample IDs) of worksheet import
and compare it to column A (location) of worksheet data. If the values
are equal I need to set a cell in worksheet data equal to the sum of
columns I:M in the same row that the sample ID = the location.

Will something like this work:

If worksheet(import).cell[D"i"] = worksheet(data).cell[A"j"]
then worksheet(data).cell[k,j] = sum(worksheet(import).[I"i":M"i"]
Is there a different, perhaps easier way to do this?
 
MSDN seems to say that range values must be written in the A1 format.

Well, yes, sort of, but...

Here is your example, restated in VBA using the Cells property.
(4, 1, 9 & 13 are your fixed columns D, A, I and M restated as numbers).
Not sure if you wanted to actually put a formula in j,k or just the result
of a formula. I chose the latter.
**********
i = 9
j = 10
k = 11
If Worksheet("import").Cells(i,4) = Worksheet("data").Cells(j,1) Then
Worksheet("data").Cells(j,k) =
WorksheetFunction.Sum(Worksheet("import").Range(Cells(i,9),Cells(i,13))
End if
**********
Note that Cells arguments follows RC notation (Row, Column) not A1 (Column,
Row).
You might want to look at the Offset property as well.

HTH,
 
I was meaning to do something like this:

Sub particlecertconv()
'
' particlecertconv Macro
' Macro recorded 12/14/2004 by Carl Porter
For i = 1 To 352
For j = 1 To 1079
For k = 2 To 9
If Worksheets("import").Cells(i, 4) = Worksheets("data").Cells(j, 1)
Then
Worksheets("data").Cells(j, k) =
WorksheetFunction.Sum(Worksheets("import").Range(Cells(i, 9), Cells(i,
13)))
End If
Next k
Next j

Next i
End Sub


It steps through well but I'm getting a run time error '1004'
application defined or object defined error when running it through
the actual spreadsheet. Thanks for your help.

Carl
 
Back
Top