Macro programming.
30 August 2006, 03:15 PM
#1
Scooby Regular
Thread Starter
Join Date: Feb 2002
Location: Lurkin Somewhere
Posts: 7,951
Likes: 0
Received 0 Likes
on
0 Posts
Macro programming.
i want a specific macro programming that i cant get my head around doing.(Not used macro in a long time)
just wonder if anyone out there is any good at programmnig these for Excel?
Cheers
S
just wonder if anyone out there is any good at programmnig these for Excel?
Cheers
S
30 August 2006, 03:39 PM
#4
Scooby Regular
Thread Starter
Join Date: Feb 2002
Location: Lurkin Somewhere
Posts: 7,951
Likes: 0
Received 0 Likes
on
0 Posts
Right,
Got ports 1-24. If the cell is yellow ignore it, it's a trunk port.
If the number is highlighted blue, i want a total at the end of the row to indicate that there is x amount of free ports on this switch.
Does that help ? not sure if i need a marco. Is that i know some how the cell properties will come into play.
Si
Got ports 1-24. If the cell is yellow ignore it, it's a trunk port.
If the number is highlighted blue, i want a total at the end of the row to indicate that there is x amount of free ports on this switch.
Does that help ? not sure if i need a marco. Is that i know some how the cell properties will come into play.
Si
30 August 2006, 04:21 PM
#5
Scooby Regular
Join Date: Sep 1999
Location: Bedfordshire
Posts: 4,037
Likes: 0
Received 0 Likes
on
0 Posts
Si,
Wouldnt it just be easier to have no number in a cell for a trunk port and a number in one that isnt and just total em up?
Or your penchant for pretty colours like yellow and pink getting the better of you? (LMAO)
Gary
Wouldnt it just be easier to have no number in a cell for a trunk port and a number in one that isnt and just total em up?
Or your penchant for pretty colours like yellow and pink getting the better of you? (LMAO)
Gary
30 August 2006, 04:38 PM
#6
Paste this in to a VBA module
Then your formula for your spreadsheet would be :-
This checks range a1:a100 for cells with the BACKGROUND colour of Red (Index 3)
This can be modified to suit your needs
Code:
Public Function CountIfColor(rng As Range, clrindx As Integer) Dim Cell CountIfColor = 0 For Each Cell In rng.Cells If Cell.Interior.ColorIndex = clrindx Then CountIfColor = CountIfColor + 1 End If Next Cell End Function
Code:
=CountIfColor(A1:A100,3)
This can be modified to suit your needs
30 August 2006, 04:44 PM
#7
Scooby Regular
Join Date: Aug 2005
Posts: 4,290
Likes: 0
Received 0 Likes
on
0 Posts
the easiest way to get to the right object code is to record a macro doing a similar thing and then go crib the code from the macro.
eg;
Dim r&, c&
c = 2
For r = 1 To 60
ActiveSheet.Cells(r, c).Select
If Selection.Interior.ColorIndex = 6 Then
ActiveSheet.Cells(r, c + 1) = "it was yellow"
End If
Next
does the job
eg;
Dim r&, c&
c = 2
For r = 1 To 60
ActiveSheet.Cells(r, c).Select
If Selection.Interior.ColorIndex = 6 Then
ActiveSheet.Cells(r, c + 1) = "it was yellow"
End If
Next
does the job
Trending Topics
Thread
Thread Starter
Forum
Replies
Last Post