Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Binary Macro
|
|
|
The UK's most regular Training information: |
|
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Binary Macro
Resolved · Urgent Priority · Version 2010
Reply Fri 13 May 2011, 10:27Delegate Salmana said...
Salmana has attended:
Excel Intermediate course
Binary Macro
Hello,
I have a spreadsheet with over 60,000 datapoints. Some cells are actual figures indicated by black, regular font. Some are estimated values which have been distinguished by making the cell italic red font.
I need to create a binary file - changing all the estimated cells into a 1, and all the actual figures into a 0.
Could you please suggest a model for a macro that might make this easier?
The data sits in cells C2:DU584 on sheet 1 of the Excel workbook.
Many thanks,
Salmana
For upcoming training course dates see: Pricing & availability
RE: Binary Macro
Hi Salmana, thanks for your query. The following subroutine should do the trick:
-----------------------
sub converttobinary
Dim myrows As Integer
Dim mycolumns As Integer
Application.ScreenUpdating = False
myrows = ActiveSheet.Range("c2").CurrentRegion.Rows.Count
mycolumns = ActiveSheet.Range("c2").CurrentRegion.Columns.Count
For rowloop = 1 To myrows
For columnloop = 1 To mycolumns
If ActiveSheet.Range("c2").Cells(rowloop, columnloop).Font.Color = 255 Then
ActiveSheet.Range("c2").Cells(rowloop, columnloop).Value = 1
Else
ActiveSheet.Range("c2").Cells(rowloop, columnloop).Value = 0
End If
Next columnloop
Next rowloop
Application.ScreenUpdating = True
end sub
-----------------------
Hope this helps,
Anthony
Reply Mon 16 May 2011, 10:10Delegate Salmana said...
RE: Binary Macro
Dear Anthony,
Many thanks for this.
When I run this an error comes up saying 'Variable not defined' highlighting the text 'rowloop'.
Would I just need to define this as Dim rowloop As Integer?
Best wishes,
Sal
RE: Binary Macro
Ah, you have Option Explicit turned on on your machine and quite right too. Add these two variable declarations below the ones already in place:
Dim rowloop As Integer
Dim columnloop As Integer
Anthony
Will be marked as resolved in 5 days
Notice: This is an automated message. Due to inactivity, this forum post will be marked as 'resolved' if there are no further responses in the next 5 days.
Tue 24 May 2011: Automatically marked as resolved.
|