Macro to read list and fill binary matrix

chrismblinn

Hello,

I am trying to tackle an idea I had for a macro. I'm very new to macros and have only created a few of them.

I have a list of dates from a 60 year time span. I have created a table, columns being years, rows being months and days. I want to read the list of dates I have then find that date in the table. Example, the list date is 1/3/1950; I need to find the 1/3 row and 1950 column and put a value of 1 in the cell. For all other dates not in the list I need values of 0.

My idea is to have the macro loop through the list. Each date it will read from the left to the second "/", and someone find the row corresponding to that date. Then it will read the first four digits from the right, and find the corresponding column. Where these two intersect, a value of 1 will be placed.

Is this possible, or am I getting way over my head?

chrismblinn

I have formatted the list of dates into 60 columns, each column corresponding to each year in the time span. These columns range from B to BI. There are a different number of dates for each year, ranging from 1 to 22 records. These records are listed in rows 370 to 392. The rows for the table I need to fill in with 0 and 1 values are indexed in Column BK from rows 2 to 366. Columns BI to DS are delegated for each year in the 60 year time span.

chrismblinn

[code]Dim obCurCell As Object, obCurCell2 As Object
Dim loX As Long

For Each obCurCell In Range("D371:D372")
For Each obCurCell2 In Range("C3:C367")
If obCurCell.Value = obCurCell2.Value Then
loX = obCurCell2.Row
Range("D" & loX) = 1
End If
Next
Next

End Sub[/code]

This code works for the first column. I need my obCurCell to loop from Column D to Column BK, and I need my obCurCell Range to loop from row 371 to 392.

DaiMitnick

Hi Chris, I think you may find it easier to simply use countif formulae and use the macro to apply the formula and copy-paste the values. However, as you already have done some work, you could just edit that sub, all you really need is to move away from using column letters and start using cell references. For instance the following code:

[code]
For Each obCurCell In Range("D371:D372")
For Each obCurCell2 In Range("C3:C367")
If obCurCell.Value = obCurCell2.Value Then
loX = obCurCell2.Row
Range("D" & loX) = 1
End If
Next
Next
[/code]

Could be changed to:

[code]
Dim i As Integer, j As Integer
For i = 371 To 372
For j = 3 To 367
If Cells(i, 4).Value = Cells(j, 3).Value Then
Cells(j, 4).Value = 1
End If
Next
Next
[/code]

Then it's a simple case of changing those hard-coded numbers to variables too, i.e. instead of that 4 there, change it to a variable called k (or whatever you like) and put in another for loop of "for k = 4 to 63" (columns D to BK). HTH, if you have any questions, please ask. Regards, Dai


------------------------------------------
Do or do not, there is no try. |
------------------------------------------