Excel VB question

[Tronsliver]

I new to this but having fun Can someone help me with the code below? I'm trying to reference a cell through using the variable "x". I know I'm using the wrong syntax but can't figure it out. [d"x"] is an attempt at a cell reference.

Sub FindDelta()

Dim x As Byte

For x = 1 To 21

If ([d"x"] > 90) Then

[f"x"] = "5"

End If
Next x
End Sub

 

[Ruairi]

Try this:

Sub FindDelta()

Dim x As Long 
Dim CellName As String 

For x = 1 To 21

CellName = "D" & Str(x) 

If (Range(CellName).Value > 90) Then

CellName = "F" & Str(x) 

Range(CellName).Value = 5

End If
Next x
End Sub

HTH Ruairi

Could your manufacturing facility benefit from real time process monitoring? Would you like your employees to be able to see up to the minute goal and actual production?
For innovative, low cost solutions check out my website.

http://www.plccontroltechnologies.com

 

[SkipVought]

Hi,
What are [d"x"] & [f"x"] supposed to represent?

Enquiring minds need to know Skip,
metzgsk@voughtaircraft.com

 

[Tronsliver]

They represent cell names. The "x" was suppose to rep the count variable which would give the other part of the cell address. I know I butchered it!

 

[SkipVought]

This is a bit more compact...

Sub FindDelta()

    Dim x As Long
    
    For x = 1 To 21
       
        If (Cells(x, "D").Value > 90) Then
        
            Cells(x, "F").Value = 5
        
        End If
    Next x
End Sub

ope this helps Skip,
metzgsk@voughtaircraft.com

 

[danomaniac]

Sub Macro1()

Dim x As Integer

For x = 1 To 20
Worksheets("sheet1&quot.Cells(2, x).Activate
If ActiveCell.Value < 1 Then
ActiveCell.Value = 5
End If
Next x


End Sub

 

[SkipVought]

danomaniac,

When referencing ROWS in Excel, you should ALWAYS use Long, since the maximum value for type Integer is 32767.

And it is a less efficient way to manipulate values in a sheet when you Select before processing. Skip,
metzgsk@voughtaircraft.com

 

[Tronsliver]

Thank you all for the code and tips...worked great.

 

[euskadi]

You can also do this:

dim myrange as text
myrange = &quot;D1&quot;& format(x, &quot;0&quot

for each mycell in activesheet.range(myrange)
if mycell.value > 90 then
mycell.offset(0,2).value = 5
end if
next mycell

Runs a bit faster also if you have big ranges to go through.

 

[danomaniac]

Skip,
Thanks for the heads-up on the variable type. Actually, that makes sense and I knew that, but I didn't think about it because in this example I was only looking at 20 rows. I know, I know. I should be consistent. You're right. I've gotten bit before by simple errors like that. Thanks for the reminder.