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