Shrink VB Code

[Emoncada]

I have the following code I wanted to know if there is a better way to do what I want it to do in less script.

If Cells(RowNum, 43).Value = "a" Then '1721 Router
ServerBuild.ChkBoxAddRouters.Value = True
Else
ServerBuild.ChkBoxAddRouters.Value = False
End If

If Cells(RowNum, 47).Value = "a" Then '2811 Router
ServerBuild.ChkBoxAddRouters.Value = True
Else
ServerBuild.ChkBoxAddRouters.Value = False
End If
If Cells(RowNum, 51).Value = "a" Then '2851 Router
ServerBuild.ChkBoxAddRouters.Value = True
Else
ServerBuild.ChkBoxAddRouters.Value = False
End If
If Cells(RowNum, 55).Value = "a" Then '2620XM Router
ServerBuild.ChkBoxAddRouters.Value = True
Else
ServerBuild.ChkBoxAddRouters.Value = False
End If

If Cells(RowNum, 59).Value = "a" Then 'K77 Router
ServerBuild.ChkBoxAddRouters.Value = True
Else
ServerBuild.ChkBoxAddRouters.Value = False
End If

If Cells(RowNum, 64).Value = "a" Then '1841 Router
ServerBuild.ChkBoxAddRouters.Value = True
Else
ServerBuild.ChkBoxAddRouters.Value = False
End If

Basically It data getting pulled from a spreadsheet into a Form.

If any of those value's have an "a" in it I would like Serverbuild.ChkBoxAddRouters.Value = True

[duluter]

How about this:

Values(0) = Cells(RowNum, 43).Value
Values(1) = Cells(RowNum, 47).Value
Values(2) = Cells(RowNum, 51).Value
Values(3) = Cells(RowNum, 55).Value
Values(4) = Cells(RowNum, 59).Value
Values(5) = Cells(RowNum, 64).Value

Serverbuild.ChkBoxAddRouters = false

for i = 0 to 5

if Values(i) = "a" then
Serverbuild.ChkBoxAddRouters.Value = true
end if

next i



Duluter

[Sagy]

How about setting column "ColNum" in "RowNum" ("x" below) to:
=If(Or(AQx="a",AUx="a",AYx="a",BCx="a",BGx="a",BLx="a"),TRUE,FALSE)

And in your VBA code all you have to do is:
Serverbuild.ChkBoxAddRouters.Value = Cells(RowNum,ColNum)

[mdmackillop]

Sub Router()
Dim r
For Each r In Array(43, 47, 51, 55, 59, 64)
ServerBuild.ChkBoxAddRouters.Value = Cells(RowNum, r).Value = "a"
Next
End Sub

[Bob Phillips]

Seeing as it is setting the same value every testr, the only prescient test is the last one, so just use



If Cells(RowNum, 64).Value = "a" Then '1841 Router
ServerBuild.ChkBoxAddRouters.Value = True
Else
ServerBuild.ChkBoxAddRouters.Value = False
End If

[mdmackillop]

Doh!!!

[duluter]

Emoncada:

Do you see what xld is saying? There may be an error in your logic.


Duluter

[Emoncada]

Yes I do I want to use something like this.


If Cells(RowNum, 43) + Cells(RowNum, 47) + Cells(RowNum, 51) + Cells(RowNum, 55) + Cells(RowNum, 59) + Cells(RowNum, 64) = 0 Then
ServerBuild.ChkBoxAddRouters.Value = False
Else
ServerBuild.ChkBoxAddRouters.Value = True
End If


would something like that work I don't think it will work the way I typed it but something similar to that.

[Tommy]

2 ways

If Cells(RowNum, 43).Value = "a" Then '1721 Router
ServerBuild.ChkBoxAddRouters.Value = True
ElseIf Cells(RowNum, 47).Value = "a" Then '2811 Router
ServerBuild.ChkBoxAddRouters.Value = True
ElseIf Cells(RowNum, 51).Value = "a" Then '2851 Router
ServerBuild.ChkBoxAddRouters.Value = True
ElseIf Cells(RowNum, 55).Value = "a" Then '2620XM Router
ServerBuild.ChkBoxAddRouters.Value = True
ElseIf Cells(RowNum, 59).Value = "a" Then 'K77 Router
ServerBuild.ChkBoxAddRouters.Value = True
ElseIf Cells(RowNum, 64).Value = "a" Then '1841 Router
ServerBuild.ChkBoxAddRouters.Value = True
Else
ServerBuild.ChkBoxAddRouters.Value = False
End If


or


If Cells(RowNum, 43).Value = "a" Or Cells(RowNum, 47).Value = "a" _
Or Cells(RowNum, 51).Value = "a" Or Cells(RowNum, 55).Value = "a" _
Or Cells(RowNum, 59).Value = "a" Or Cells(RowNum, 64).Value = "a" Then '1721 Router
ServerBuild.ChkBoxAddRouters.Value = True
Else
ServerBuild.ChkBoxAddRouters.Value = False
End If

[mdmackillop]

Sub Router()
Dim r, txt As String

For Each r In Array(43, 47, 51, 55, 59, 64)
txt = txt & Cells(RowNum, r).Value
Next
If InStr(1, txt, "a") > 0 Then ServerBuild.ChkBoxAddRouters.Value = True
End Sub

[Bob Phillips]

Is it really 64, not 63. Every other step is 4 columns.

[Emoncada]

Your right XLD it's 63 it was a typo.

[Bob Phillips]

Then you can use



ServerBuild.ChkBoxAddRouters.Value = Application.Evaluate( _
"SUMPRODUCT(--(MOD(COLUMN(AQ" & RowNum & ":BK" & RowNum & "),4)=3),--(AQ" & RowNum & ":BK" & RowNum & "=""a""))") > 0