How do I add the E column basd on the F column

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

The following code steps through all the worksheets and all the cells in the
range f2 thru f40, If the value in Fx = projectname I want to add the values
in the cell to the left, Ex. Using the for each statement is nice but how do
I get the info from Ex if Fx = projectname. I have tried to make E a variable
based on Cel, a collection object, but I keep getting errrors.

This is the line that is crashing:
TotalHoursToDate.Text = CDbl(TotalHoursToDate.Text) + CDbl(Range("cel.Column
- 1" & "cel.Row")) ' does not work - ie. F20 & E20

It would look like this:
if projectname = bell then
Total = Total + E20
end if

This is the part of that line that I need to work but doesn't:
CDbl(Range("cel.Column - 1" & "cel.Row")) ' ie. F20 & E20



'MsgBox (1)
Dim sht As Worksheet
Dim cel As Range
Dim j, column As Integer
For Each sht In Worksheets
For Each cel In sht.Range("F2:F40")
column = E
MsgBox (Cells("column & cel.Row"))
If cel.Text = TotalHours.Projects.Text Then
TotalHoursToDate.Text = CDbl(TotalHoursToDate.Text) +
CDbl(Range("cel.Column - 1" & "cel.Row")) ' does not work
'TotalHoursToDate.Text = CDbl(TotalHoursToDate.Text) +
CDbl(TotalHoursToDate.Text) ' works

End If
'MsgBox (cel.Row)
Next cel
Next sht


Thank you for the help. I have searched thru meny articles but I cant apply
their suggestion of the for each in this case!
 
CDbl(Range("cel.Column - 1" & "cel.Row"))...
Enclosing the cel.row and cel.column in quotes will prevent Excel from
evaluating the expressions. It's not clear to me whether cel.Column is
numeric or alpha. If numeric, I think you'll need
CDbl(cells(cel.Row,cel.Column-1)). If alpha, then I think you can just drop
the quotes.
 
Thanks for the quick reply! I will try this and write back tomarrow if I have
made that work. I have been trying for hours to do this one thing!
 
Hey I just tried it and I am getting an

error 1004
Method 'Range' of Object '_Global' failed.

This is the error that I keep getting with the range object
 
Which 'it' did you try? What's the value of cel.column. As noted, if it's a
number, use Cells rather than Range; ex: CDbl(cells(cel.Row,cel.Column-1)).
 
Back
Top