Slow VBA codes when analyzing stockmarket

E

espenskeie

Well-known Member
Joined
Mar 30, 2009
Messages
636
Office Version
  1. 2016
Platform
  1. Windows
Hello

I have a code that runs very slow, and I have reason to believe that it is because I have some loops running through the rows of stockdata.

Would it be faster if I used formulas with IF-statements instead of all the loops and If-statment in my VBA procedure?

For those of you that care, here are the code I'm running, it's long:

Code: 
Sub MovingAverageCalc()
Dim myCol As Long
Dim mov1 As Long, mov2 As Long, mov3 As Long, mov4 As Long
Dim alpha1 As Double, alpha2 As Double, alpha3 As Double, alpha4 As Double
Dim slLong As Long, slShort As Long
Dim ATR As Double
Dim obx_string As String, obx_file As String, folderXXX As String
Dim lr As Long, lr0 As Long, lr1 As Long, lr1a As Long
Dim lr1aUp As Long, lr1b As Long
Dim i As Long, j As Long, l As Long, x As Long, y As Long, z As Long, t As Long, u As Long
Dim ws As Worksheet, ws1 As Worksheet
Dim ws1a As Worksheet
Dim wb As Workbook, wbOBX As Workbook
Dim limit As Double
Dim days As Long, dayshold As Long
Dim xDate As Long, xDate2 As Long, yDate As Long, yDate2 As Long
Dim startdate As Date
Dim enddate As Date
Dim fstdate As Range, lstdate As Range
Dim rolldateX As Range, rolldateY As Range
Dim rngA As Range, rngB As Range, rngC As Range
Dim originalSD As Range, originalED As Range
Dim entryval As Double, exitval As Double


Set wb = ThisWorkbook
Set ws = wb.Sheets("Master")
Set ws1 = wb.Sheets("MA")
Set ws1a = wb.Sheets("Data_MA")


lr1b = ws1a.Range("A65536").End(xlUp).Row
If lr1b < 3 Then lr1b = 3


ws1a.Range("A3:AK" & lr1b).Clear


Application.ScreenUpdating = False


With ws
myCol = .Range("IV2").End(xlToLeft).Column


mov1 = .Range("C8")
mov2 = .Range("C9")
mov3 = .Range("C10")
mov4 = .Range("C11")


alpha1 = .Range("E8")
alpha2 = .Range("E9")
alpha3 = .Range("E10")
alpha4 = .Range("E11")


slLong = .Range("D20")
slShort = .Range("D21")
End With


For x = 8 To myCol
    lr0 = ws.Cells(63556, x).End(xlUp).Row + 1
For F = 3 To lr0


'***EOD for den respektive aksjen***
        'folderXXX = "D:\" & ws.Cells(2, x) & "\"
        folderXXX = ws.Range("G2") & ws.Cells(2, x) & "\"
        obx_file = ws.Cells(F, x).Value
        'obx_string = "D:\" & folderXXX & "\" & obx_file & ".csv"
        obx_string = ws.Range("G2") & folderXXX & "\" & obx_file & ".csv"


strPath = folderXXX
strFilename = obx_file & ".csv"


If Len(Dir(strPath & strFilename)) > 0 Then
On Error GoTo Lst1:
      Set wbOBX = Workbooks.Open(strPath & strFilename)
On Error GoTo 0
        Set wbOBX = Workbooks(strFilename)
        Set wsOBX = wbOBX.Sheets(obx_file)
        
        wsOBX.Columns("B").AutoFit
            
            lr1 = wsOBX.Range("A2").End(xlDown).Row
            lr2 = ws1.Range("B3").End(xlDown).Row
        
                ws1.Range("A3:H" & lr2).Clear
                ws1.Range("I19:AJ" & lr2).Clear
                
            Set fstdate = Nothing
            Set lstdate = Nothing
            
            startdate = ws.Range("C2").Value
            Res = Application.Match(CLng(startdate), wsOBX.Range("B2:B" & lr1), 0)


            If Not IsError(Res) Then
               Set fstdate = wsOBX.Range("B" & Res + 1)
            Else
                For t = 1 To 60
                startdate = startdate + 1
                Res = Application.Match(CLng(startdate), wsOBX.Range("B2:B" & lr1), 0)
                
                If Not IsError(Res) Then
                Set fstdate = wsOBX.Range("B" & Res + 1)
                    Exit For
                End If
                Next t
            
            End If
            
            enddate = ws.Range("C3").Value
            Res = Application.Match(CLng(enddate), wsOBX.Range("B2:B" & lr1), 0)


            If Not IsError(Res) Then
               Set lstdate = wsOBX.Range("B" & Res + 1)
            Else
                For u = 1 To 60
                enddate = enddate - 1
                Res = Application.Match(CLng(enddate), wsOBX.Range("B2:B" & lr1), 0)
                
                If Not IsError(Res) Then
                Set lstdate = wsOBX.Range("B" & Res + 1)
                    Exit For
                End If
                Next u
            End If
            
    If Not fstdate Is Nothing And Not lstdate Is Nothing Then
                    On Error GoTo Lst:
                    firstRow = fstdate.Row
                    lastRow = lstdate.Row
                    On Error GoTo 0
        Else: GoTo Lst:
    End If
            ws.Activate
                            
ws1.Columns("A:AE").Clear


wsOBX.Range("A" & firstRow & ":H" & lastRow).Copy Destination:=ws1.Range("A2")
                        
lr = ws1.Range("A2").End(xlDown).Row


ws1.Activate
ws1.Range("W3:X10").Clear


With ws
    limit = .Range("X2").Value
    days = .Range("X1").Value
    dayshold = .Range("Z1").Value
End With


With ws1
    .Range("I2").Formula = "=IFERROR((G2-F2)/(E2-F2),0)"
    .Range("I2").AutoFill Destination:=.Range("I2" & ":I" & lr)


'Moving Averages
            .Range("I1").Value = "(c-l)/(h-l)"
            
            
            .Range("J1").Value = ws.Range("B8").Value & ws.Range("C8").Value
            .Range("K1").Value = ws.Range("B9").Value & ws.Range("C9").Value
            .Range("L1").Value = ws.Range("B10").Value & ws.Range("C10").Value
            .Range("M1").Value = ws.Range("B11").Value & ws.Range("C11").Value
            
            
    If ws.Range("B8").Value = "MA" Then
        .Range("J" & mov1 + 1).Formula = "=AVERAGE(G2:G" & mov1 + 1 & ")"
        .Range("J" & mov1 + 1).NumberFormat = "0.00"
        .Range("J" & mov1 + 1).AutoFill Destination:=.Range("J" & mov1 + 1 & ":J" & lr)
    Else
        .Range("J" & mov1 + 1).Formula = "=AVERAGE(G2:G" & mov1 + 1 & ")"
        .Range("J" & mov1 + 2).Formula = "=" & alpha1 & "*J" & mov1 + 1 & "+(1-" & alpha1 & ")*G" & mov1 + 2 & ""
        .Range("J" & mov1 + 2).NumberFormat = "0.00"
        .Range("J" & mov1 + 2).AutoFill Destination:=.Range("J" & mov1 + 2 & ":J" & lr)
    End If
    If ws.Range("B9").Value = "MA" Then
        .Range("K" & mov2 + 1).Formula = "=AVERAGE(G2:G" & mov2 + 1 & ")"
        .Range("K" & mov2 + 1).NumberFormat = "0.00"
        .Range("K" & mov2 + 1).AutoFill Destination:=.Range("K" & mov2 + 1 & ":K" & lr)
    Else
        .Range("K" & mov2 + 1).Formula = "=AVERAGE(G2:G" & mov2 + 1 & ")"
        .Range("K" & mov2 + 2).Formula = "=" & alpha2 & "*K" & mov2 + 1 & "+(1-" & alpha2 & ")*G" & mov2 + 2 & ""
        .Range("K" & mov2 + 2).NumberFormat = "0.00"
        .Range("K" & mov2 + 2).AutoFill Destination:=.Range("K" & mov2 + 2 & ":K" & lr)
    End If
    If ws.Range("B10").Value = "MA" Then
        .Range("L" & mov3 + 1).Formula = "=AVERAGE(G2:G" & mov3 + 1 & ")"
        .Range("L" & mov3 + 1).NumberFormat = "0.00"
        .Range("L" & mov3 + 1).AutoFill Destination:=.Range("L" & mov3 + 1 & ":L" & lr)
    Else
        .Range("L" & mov3 + 1).Formula = "=AVERAGE(G2:G" & mov3 + 1 & ")"
        .Range("L" & mov3 + 2).Formula = "=" & alpha3 & "*L" & mov3 + 1 & "+(1-" & alpha3 & ")*G" & mov3 + 2 & ""
        .Range("L" & mov3 + 2).NumberFormat = "0.00"
        .Range("L" & mov3 + 2).AutoFill Destination:=.Range("L" & mov3 + 2 & ":L" & lr)
    End If
    If ws.Range("B11").Value = "MA" Then
        .Range("M" & mov4 + 1).Formula = "=AVERAGE(G2:G" & mov4 + 1 & ")"
        .Range("M" & mov4 + 1).NumberFormat = "0.00"
        .Range("M" & mov4 + 1).AutoFill Destination:=.Range("M" & mov4 + 1 & ":M" & lr)
    Else
        .Range("M" & mov4 + 1).Formula = "=AVERAGE(G2:G" & mov4 + 1 & ")"
        .Range("M" & mov4 + 2).Formula = "=" & alpha4 & "*M" & mov4 + 1 & "+(1-" & alpha4 & ")*G" & mov4 + 2 & ""
        .Range("M" & mov4 + 2).NumberFormat = "0.00"
        .Range("M" & mov4 + 2).AutoFill Destination:=.Range("M" & mov4 + 2 & ":M" & lr)
    End If
  '***************************************************
  '***************************************************
  '***************************************************
  '***************************************************
  '***************************************************
  '***************************************************
        
        .Range("R1").Value = "Long %"
        .Range("S1").Value = "Short %"
        .Range("AA1").Value = "Long entry"
        .Range("AB1").Value = "Short entry"
        .Range("AC1").Value = "Long count"
        .Range("AD1").Value = "Short count"
        .Range("AE1").Value = "H - L"
        .Range("AF1").Value = "|H - Cp|"
        .Range("AG1").Value = "|L - Cp|"
        .Range("AH1").Value = "TR"
        .Range("AI1").Value = "ATR"
        
                '**************A**T**R*****************
                .Range("AE2").Formula = "=E2-F2"
                .Range("AE3").Formula = "=E3-F3"
                .Range("AF3").Formula = "=ABS(E3-G2)"
                .Range("AG3").Formula = "=ABS(F3-G2)"
                .Range("AH2").Formula = "=MAX(AC2:AE2)"
                .Range("AH3").Formula = "=MAX(AC3:AE3)"
                
                .Range("AE3:AH3").AutoFill Destination:=.Range("AE3:AH" & lr)
                
                .Range("AI15").Formula = "=AVERAGE(AH2:AH15)"
                .Range("AI15").AutoFill Destination:=.Range("AI15:AI" & lr)
        
            For i = mov3 + 2 To lr
        'On Error GoTo Lst:
            If .Range("G" & i).Value > .Range("L" & i).Value Then ' Long check
                If .Range("G" & i - 1).Value < .Range("L" & i - 1).Value Then
                    entryval = .Range("D" & i + 1).Value ' Entryvalue next day on Open
                    If Not entryval = 0 Then
                        .Range("AA" & i + 1).Value = entryval
                    For j = i + 1 To lr
                        If Not j = lr Then
                        ATR = .Range("AI" & j).Value
                        If entryval - .Range("E" & j).Value > ATR * slLong Then 'STOPLOSS check
                            exitval = entryval - (ATR * slLong) ' STOPLOSS -> Exitvalue next day on Open
                            .Range("R" & j + 1).Value = (exitval - entryval) / entryval
                            .Range("AC" & j + 1).Value = j - i
                            .Range("AA" & j + 1).Value = "StopLoss"
                            i = j + 1
                            Exit For
                        Else
                                If .Range("K" & j).Value > .Range("L" & j).Value Then
                                exitval = .Range("D" & j + 1).Value ' Exitvalue next day on Open
                                .Range("R" & j + 1).Value = (exitval - entryval) / entryval
                                .Range("AC" & j + 1).Value = j - i
                                i = j + 1
                                Exit For
                                    If Not exitval = 0 Then 'Checking if open value exist, if not then try next row
                                        .Range("R" & j + 1).Value = (exitval - entryval) / entryval
                                        .Range("AC" & j + 1).Value = j - i
                                        i = j + 1
                                        Exit For
                                    Else
                                    exitval = .Range("D" & j + 2).Value
                                        If Not exitval = 0 Then
                                            .Range("R" & j + 2).Value = (exitval - entryval) / entryval
                                            .Range("AC" & j + 1).Value = j - i
                                            i = j + 2
                                            Exit For
                                        Else
                                        .Range("AA" & i + 1).Clear
                                        Exit For
                                        End If
                                    End If
                                End If
                            End If
                        Else
                            .Range("AA" & i + 1).Clear
                            Exit For
                        End If
                    Next j
                    End If
                End If
            ElseIf .Range("G" & i).Value < .Range("L" & i).Value Then ' Short check
                If .Range("G" & i - 1).Value > .Range("L" & i - 1).Value Then
                entryval = .Range("D" & i + 1).Value ' Entryvalue next day on Open
                    If Not entryval = 0 Then
                        .Range("AB" & i + 1).Value = entryval
                    For j = i + 1 To lr
                        If Not j = lr Then
                            If .Range("K" & j).Value < .Range("L" & j).Value Then
                                exitval = .Range("D" & j + 1).Value ' Exitvalue next day on Open
                                    If Not exitval = 0 Then 'Checking if open value exist, if not then try next row
                                        .Range("S" & j + 1).Value = (entryval - exitval) / entryval
                                        .Range("AD" & j + 1).Value = j - i
                                        i = j + 1
                                        Exit For
                                    Else
                                    exitval = .Range("D" & j + 2).Value
                                        If Not exitval = 0 Then
                                            .Range("R" & j + 2).Value = (entryval - exitval) / entryval
                                            .Range("AD" & j + 1).Value = j - i
                                            i = j + 2
                                            Exit For
                                        Else
                                        .Range("AB" & i + 1).Clear
                                        Exit For
                                        End If
                                    End If
                                End If
                        Else
                            .Range("AB" & i + 1).Clear
                            Exit For
                        End If
                    Next j
                    End If
                End If
            End If
    Next i
 
            .Range("T1").Value = "Accumulated %"
            .Range("T2").Formula = "=SUM(R2+S2)"
            .Range("T3").Formula = "=SUM(R3+S3+T2)"
            .Range("T3").AutoFill Destination:=.Range("T3:T" & lr)
            
            .Range("U1").Value = "Acc Long"
            .Range("U2").Formula = "=R2"
            .Range("U3").Formula = "=SUM(R3+U2)"
            .Range("U3").AutoFill Destination:=.Range("U3:U" & lr)
            
            .Range("V1").Value = "Acc Short"
            .Range("V2").Formula = "=S2"
            .Range("V3").Formula = "=SUM(S3+V2)"
            .Range("V3").AutoFill Destination:=.Range("V3:V" & lr)
            
            'Long only data
            .Range("Z2").Formula = "=0"
            .Range("Z3").Formula = "=SUM(G3-G2)/G2+Z2"
            .Range("Z3").AutoFill Destination:=.Range("Z3:Z" & lr)
            
             
        .Names.Add Name:="Accumulated", RefersToR1C1:= _
        "='MA'!R2C20:R" & lr & "C20"
        .Names("Accumulated").Comment = ""
            .Names.Add Name:="Long_only", RefersToR1C1:= _
            "='MA'!R2C21:R" & lr & "C21"
            .Names("Long_only").Comment = ""
        .Names.Add Name:="Short", RefersToR1C1:= _
        "='MA'!R2C22:R" & lr & "C22"
        .Names("Short").Comment = ""
            .Names.Add Name:="MAThirteen", RefersToR1C1:= _
            "='MA'!R2C11:R" & lr & "C11"
            .Names("MAThirteen").Comment = ""
        .Names.Add Name:="MATwenty", RefersToR1C1:= _
        "='MA'!R2C12:R" & lr & "C12"
        .Names("MATwenty").Comment = ""
      End With
        
''''''**************************************************************************************************************
  
            lookback1 = ws.Range("C4").Value
            lookback2 = ws.Range("C5").Value
            lookback3 = ws.Range("C6").Value
            
            j = ws1a.Range("A65536").End(xlUp).Row + 1
                                        
            With ws1a
                
                .Range("A" & j).Value = ws1.Range("A2").Value
                .Range("B" & j).Value = lr
'*************************L**O**N**G***********************************************************************************
                If Application.WorksheetFunction.Sum(ws1.Range("AC4:AC" & lr)) <> 0 Then
                .Range("C" & j).Value = Application.WorksheetFunction.Average(ws1.Range("AC4:AC" & lr))
                End If
                
                .Range("D" & j).Value = Application.WorksheetFunction.Sum(ws1.Range("R4:R" & lr).Value) 'Sum long
                
            If Not lr < lookback3 + lookback2 Then
                .Range("E" & j).Value = Application.WorksheetFunction.Sum(ws1.Range("R" & lr - lookback1 & ":R" & lr).Value) '0-90 days
                .Range("F" & j).Value = Application.WorksheetFunction.Sum(ws1.Range("R" & lr - lookback2 & ":R" & lr - lookback1).Value) '90-180 days
                .Range("G" & j).Value = Application.WorksheetFunction.Sum(ws1.Range("R" & lr - lookback3 & ":R" & lr - lookback2).Value) ' 180-360 days
            Else
                .Range("E" & j).Value = "Lookback!"
                .Range("F" & j).Value = "Lookback!"
                .Range("G" & j).Value = "Lookback!"
            End If
            
                .Range("H" & j).Value = Application.WorksheetFunction.Max(ws1.Range("R4:R" & lr).Value)
                .Range("I" & j).Value = Application.WorksheetFunction.Min(ws1.Range("R4:R" & lr).Value)
                
                If Application.WorksheetFunction.SumIf(ws1.Range("R4:R" & lr), "<>0") <> 0 Then
                    .Range("J" & j).Value = Application.WorksheetFunction.AverageIf(ws1.Range("R4:R" & lr), "<>0") 'Total long average
                End If
                
                If Application.WorksheetFunction.SumIf(ws1.Range("R4:R" & lr), ">0") <> 0 Then
                    .Range("K" & j).Value = Application.WorksheetFunction.AverageIf(ws1.Range("R4:R" & lr), ">0") 'Average winners
                End If
                
                If Application.WorksheetFunction.SumIf(ws1.Range("R4:R" & lr), "<0") <> 0 Then
                .Range("L" & j).Value = Application.WorksheetFunction.AverageIf(ws1.Range("R4:R" & lr), "<0") 'Average losers
                Else
                .Range("L" & j).Value = 0
                End If
                
                .Range("M" & j).Value = Application.WorksheetFunction.CountIf(ws1.Range("R4:R" & lr), ">0") 'Number of winners
                .Range("N" & j).Value = Application.WorksheetFunction.CountIf(ws1.Range("R4:R" & lr), "<0") ' Number of losers
 
 '*************************S**H**O**R**T*************************************************************************************
                If Application.WorksheetFunction.Sum(ws1.Range("AD4:AD" & lr)) <> 0 Then
                .Range("O" & j).Value = Application.WorksheetFunction.Average(ws1.Range("AD4:AD" & lr))
                End If
                
                .Range("P" & j).Value = Application.WorksheetFunction.Sum(ws1.Range("S4:S" & lr).Value) 'Sum short
                
            If Not lr < lookback3 + lookback2 Then
                .Range("Q" & j).Value = Application.WorksheetFunction.Sum(ws1.Range("S" & lr - lookback1 & ":S" & lr).Value) '0-90 days
                .Range("R" & j).Value = Application.WorksheetFunction.Sum(ws1.Range("S" & lr - lookback2 & ":S" & lr - lookback1).Value) '90-180 days
                .Range("S" & j).Value = Application.WorksheetFunction.Sum(ws1.Range("S" & lr - lookback3 & ":S" & lr - lookback2).Value) ' 180-360 days
            Else
                .Range("Q" & j).Value = "Lookback!"
                .Range("R" & j).Value = "Lookback!"
                .Range("S" & j).Value = "Lookback!"
            End If
            
                .Range("T" & j).Value = Application.WorksheetFunction.Max(ws1.Range("S4:S" & lr).Value)
                .Range("U" & j).Value = Application.WorksheetFunction.Min(ws1.Range("S4:S" & lr).Value)
                
                If Application.WorksheetFunction.SumIf(ws1.Range("S4:S" & lr), "<>0") <> 0 Then
                    .Range("V" & j).Value = Application.WorksheetFunction.AverageIf(ws1.Range("S4:S" & lr), "<>0") 'Total short average
                End If
                
                If Application.WorksheetFunction.SumIf(ws1.Range("S4:S" & lr), ">0") <> 0 Then
                    .Range("W" & j).Value = Application.WorksheetFunction.AverageIf(ws1.Range("S4:S" & lr), ">0") 'Average winners
                End If
                
                If Application.WorksheetFunction.SumIf(ws1.Range("S4:S" & lr), "<0") <> 0 Then
                .Range("X" & j).Value = Application.WorksheetFunction.AverageIf(ws1.Range("S4:S" & lr), "<0") 'Average losers
                Else
                .Range("X" & j).Value = 0
                End If
                
                .Range("Y" & j).Value = Application.WorksheetFunction.CountIf(ws1.Range("S4:S" & lr), ">0") 'Number of winners
                .Range("Z" & j).Value = Application.WorksheetFunction.CountIf(ws1.Range("S4:S" & lr), "<0") ' Number of losers
                
'****************************************************************************************************************************
                
                If Application.WorksheetFunction.Sum(ws1.Range("H4:H" & lr)) <> 0 Then
                .Range("AD" & j).Value = Application.WorksheetFunction.Average(ws1.Range("H4:H" & lr))
                End If
                .Range("AK" & j).Value = ws.Cells(2, x)
               '***********R-Multiple Long
                If Not .Range("L" & j).Value = 0 Then
                .Range("AH" & j).Value = .Range("K" & j).Value / -.Range("L" & j).Value
                Else
                .Range("AH" & j).Value = "Win only"
                End If
                '***********R-Multiple Short
                If Not .Range("X" & j).Value = 0 Then
                .Range("AI" & j).Value = .Range("W" & j).Value / -.Range("X" & j).Value
                Else
                .Range("AI" & j).Value = "Win only"
                End If
                
                
                .Range("AJ" & j).Value = ws1.Range("Z" & lr).Value
                End With
Lst:
                On Error Resume Next
                    wbOBX.Close False
                On Error GoTo 0
    Else
Lst1:
                ws.Cells(F, x).Delete 'If obx_string doesn't exist....
        
    End If
        Next F
                ws.Activate
                lrZ = ws.Cells(63356, x).End(xlUp).Row
                On Error Resume Next
                ws.Range(Cells(3, x), Cells(lrZ + 1, x)).SpecialCells(xlCellTypeBlanks).Delete Shift:=xlUp
                On Error GoTo 0
        Next x
'******************************************************************************************************************************************
        On Error Resume Next
        With ws1a
            .Activate
            .Range("C3:C" & j).NumberFormat = "0.00"
            .Range("D3:L" & j).NumberFormat = "0.00%"
            .Range("O3:O" & j).NumberFormat = "0.00"
            .Range("P3:X" & j).NumberFormat = "0.00%"
            .Range("AD3:AD" & j).NumberFormat = "0,000"
            .Range("AH3:AI" & j).NumberFormat = "0.00"
            .Range("AJ3:AJ" & j).NumberFormat = "0.00%"
            .Range("A3:A" & j).Select
        End With
        
        Call color_second_rowsMA
        Call Add_condformMA


        Application.ScreenUpdating = True
        
        End Sub

When I remove this lines:
Code: 
.Range("AE1").Value = "H - L"
        .Range("AF1").Value = "|H - Cp|"
        .Range("AG1").Value = "|L - Cp|"
        .Range("AH1").Value = "TR"
        .Range("AI1").Value = "ATR"
        
                '**************A**T**R*****************
                .Range("AE2").Formula = "=E2-F2"
                .Range("AE3").Formula = "=E3-F3"
                .Range("AF3").Formula = "=ABS(E3-G2)"
                .Range("AG3").Formula = "=ABS(F3-G2)"
                .Range("AH2").Formula = "=MAX(AC2:AE2)"
                .Range("AH3").Formula = "=MAX(AC3:AE3)"
                
                .Range("AE3:AH3").AutoFill Destination:=.Range("AE3:AH" & lr)
                
                .Range("AI15").Formula = "=AVERAGE(AH2:AH15)"
                .Range("AI15").AutoFill Destination:=.Range("AI15:AI" & lr)

The code runs very fast.... Why???


Kind regards
Espen
 
Last edited:

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying
You must log in or register to reply here.

Similar threads

O
  • Question Question
Creating workbooks for a team of staff
Replies
1
Views
188
HighAndWilder
H
N
  • Question Question
Finding the differences then copy and paste code to a new sheet
Replies
1
Views
582
myall_blues
myall_blues
R
  • Question Question
[VBA] - Need to Hide one column and need sum of columns
Replies
0
Views
182
rsolanki
R
S
  • Question Question
Error message when executing macro
Replies
3
Views
306
rlv01
R
K
  • Solved
Previously working very complex VBA now stopped
Replies
7
Views
510
Alex Blakenburg
A

Forum statistics

Threads
1,223,249
Messages
6,171,031
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top