PDA

View Full Version : Shrink VB Code



Emoncada
02-05-2009, 09:34 AM
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
02-05-2009, 09:39 AM
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
02-05-2009, 11:04 AM
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
02-05-2009, 02:31 PM
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

xld
02-05-2009, 04:28 PM
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
02-05-2009, 04:34 PM
Doh!!!

duluter
02-05-2009, 04:45 PM
Emoncada:

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


Duluter

Emoncada
02-06-2009, 06:20 AM
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
02-06-2009, 06:38 AM
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
02-06-2009, 07:00 AM
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

xld
02-06-2009, 07:42 AM
Is it really 64, not 63. Every other step is 4 columns.

Emoncada
02-06-2009, 12:11 PM
Your right XLD it's 63 it was a typo.

xld
02-06-2009, 12:40 PM
Then you can use



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