autofill (1 Viewer)

myrt

Registered User.
Local time
Today, 16:28
Joined
Apr 22, 2015
Messages
34
Hi, I absolutely need your help.
I put down a function. The function seems to work as intended. What I can't do is to extend the function to entire column. Help!

Code:
[Public Function ADI() As Variant

Dim cv, N, Tot As Integer
Dim sourcerange, fillrange As Range
Dim lastrow As Integer

cv = 0
N = 0
Tot = 0
      
  If Cells(ActiveCell.Row, 2).Value = "0" Then
  cv = cv + 1
  End If

  For i = 3 To 18
      If Cells(ActiveCell.Row, i).Value = "0" Then
      cv = cv + 1
      Else
      Tot = Tot + cv
      N = N + 1
      cv = 0
      End If
  Next i
  
  Tot = Tot + cv
  N = N + 1
     
  If IsNull(N) = False Then
  ADI = Tot / N
  Else
  ADI = "needs deeper understanding"
  End If

'what I tried:
'lastrow = Sheets("Sells_kg").Cells(Rows.Count, 1).End(xlUp).Row
'Set sourcerange = ActiveCell
'Set fillrange = Range(ActiveCell.Address, Cells(lastrow, ActiveCell.Column)) / Set fillrange = Range(ActiveCell, ActiveCell.End(xlDown))
'sourcerange.AutoFill Destination:=fillrange

End Function
 

Rx_

Nothing In Moderation
Local time
Today, 09:28
Joined
Oct 22, 2009
Messages
2,803
Important: this process changes the data - but doesn't leave any trace of the formula in the worksheet. It just leaves the results of the data in the worksheet.

Basically, treat each Excel Row as a record.
Here is a code segment that looks at a cell's value then makes some decision about what other cells should do. The logic basically turns other cells Bold.
Of course you could substitute your code that starts with Active Cell.

The variable intRowPos - keeps track of the current Row in the Workbook
intMaxRecordCount - When I bring in a Recordset from an Access Query into Excel, the RecordCount is assigned to this. That way the number of records is known.
intRowPos - I like to put my Header (titles) in Excel on worksheet row 5 and start the data on row 6. It leaves me room for titles and other dashboard information on top of the data. If your header is on Row 1, then intRowPos is 2 (where the data starts).
The variable i is just an integer counter for the loop
ObjXL is just setting an object reference to Excel.
Dim objXL As Excel.Application

Code:
2930    With objxl.ActiveWorkbook.ActiveSheet
2940      For i = intRowPos To intMaxRecordCount + intRowPos
2950          If .Cells(i, "B").Value <> .Cells(i - 1, "B").Value Then
2960              .Range(.Cells(i, "B"), .Cells(i, "E")).Font.FontStyle = Bold
' 2970              .Cells(i, 33).Value = .Cells(i, 3).Value ' alternative yet speedy method of moving data - with some formula 
2980          Else
2990               .Range(.Cells(i, "B"), .Cells(i, "E")).Font.ColorIndex = 16 metalic gray
3000          End If
3010      Next i
3020  End With

This is an extremely simple process.
On my extremely complex multiple formula large datasets it is advisable to
1. Turn off worksheet updating ' this can vastly speed up the process
2. Turn visible to false until finished.
 

Users who are viewing this thread

Top Bottom