| Forum Home | ||||
| Press F1 | ||||
| Thread ID: 27001 | 2002-11-10 22:23:00 | excel column puzzle | santae (2502) | Press F1 |
| Post ID | Timestamp | Content | User | ||
| 97653 | 2002-11-13 21:10:00 | Thankyou for your macro. I need help putting it in. There are 38 data columns at H2 to AS. I can place a commandButton1_Click(), and add code. I do not know what to use to display the results. There would be one display for each column. Appreciate your help |
santae (2502) | ||
| 97654 | 2002-11-13 21:18:00 | Thankyou for your code. Your solution is much appreciated. cheers, Santae |
santae (2502) | ||
| 97655 | 2002-11-13 22:50:00 | The above was just example code to see if its generally what you want . It checks column A and sees if there are blanks then after going through to the last cell which contains data, it then produces a message box saying how many were found and uses a comma to separate the values found . Changing the output (into a cell/s etc instead of a message box) isnt hard to change, but first you need to check it captures the figures you want . It will be more difficult if you want to look at individual blanks between cells in other columns (eg h2= data, i2 = blank, j2 = data) as opposed to looking directly down a column (h2, h3, h4 etc) . I have adjusted the code slightly to look in column H as a test . To add the code either create a command button and assign macro and paste the code below in, or alternatively select Tools-Macro-Visual Basic Editor, select Insert-Module then paste the code in underneath the words Option Explicit . To run the macro, select Tools-Macro-Macros and select the macro named FindBlank and then the Run button . Sub FindBlank() Dim rng As Range Dim cell Dim count As Integer Dim YeTotals As String 'Initialize variables Set rng = Range(Range("h2"), Range("h" & Range("a65536") . End(xlUp) . Row)) count = 0 'count # of blank cells between non-blank cells in range For Each cell In rng If cell . Value = "" Then count = count + 1 End If If cell . Offset(1, 0) . Value <> "" Then YeTotals = YeTotals & "," & count count = 0 End If Next cell MsgBox "blanks totals are " & YeTotals End Sub |
parry (27) | ||
| 97656 | 2002-11-13 22:55:00 | Doh, I made a mistake... the line that has ... Set rng = Range(Range("h2"), Range("h" & Range("a65536").End(xlUp).Row)) change this to.... Set rng = Range(Range("h2"), Range("h" & Range("h65536").End(xlUp).Row)) |
parry (27) | ||
| 97657 | 2002-11-14 19:58:00 | Option Explicit Dim rng As Range Dim cell Dim count As Integer Dim Yetotals As String "Inialize variables Set........................ -I ran the macro Findblank -A compile error occurred at this point above. - Set was highlighted in blue. -Invalid outside procedure Help |
santae (2502) | ||
| 97658 | 2002-11-14 23:00:00 | Strange, works perfectly fine for me with no runtime errors. You should have Sub FindBlank() between option explicit and Dim rng as Range. Make sure you copy all the code in my previous email starting from Sub FindBank() and up to and including end sub. | parry (27) | ||
| 97659 | 2002-11-15 19:12:00 | Thankyou parry for your help. Friday is a busy day. I will work on the macro today and get back to you. Thanks |
tawhiti (2503) | ||
| 97660 | 2002-11-16 04:58:00 | Have created a custom menu with 38 cmdbtn's. One for each column. Thanks. |
santae (2502) | ||
| 97661 | 2002-11-19 00:14:00 | I wish to take the macro you coded a bit further, and have four new macros that sort the results of above macro. You have the power and santae will pay$upfront to rid himself of hours of frustration and overeating. Please contact santae@xtra.co.nz |
santae (2502) | ||
| 97662 | 2002-11-19 00:32:00 | Hi Santae, I dont think money will be necessary. I'll email you. cheers Parry |
parry (27) | ||
| 1 2 3 | |||||