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.

[VBA] 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[/VBA]

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 [VBA]Serverbuild.ChkBoxAddRouters.Value = True[/VBA]

[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:
[VBA]Serverbuild.ChkBoxAddRouters.Value = Cells(RowNum,ColNum)[/VBA]

[mdmackillop]

[VBA]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
[/VBA]

[Bob Phillips]

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

[vba]

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

[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.

[vba]
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
[/vba]

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
[VBA]
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
[/VBA]

or

[VBA]
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
[/VBA]

[mdmackillop]

[VBA]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
[/VBA]

[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

[vba]

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