Clicking cells from VBA

[gordonlyle]

Hi clever folks,

Just a very quick query, is it possible to click a cell rather than typing in the cell ref?

Rather than typing in: Range ("A1") I would like to type Range(" and then click the cell in excel to fill in the range details.

I know this is lazy but I have a lot of very small cells in an incredibly complicated workbook and I have to do this A LOT!

Thank you in advance for you brain power!!!

Gordon

 

[Ramballah]

basically he's saying to make your code easier to update. like this:

Sub CopyToCorrectRanges()    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Dim rng1 As Range
    Dim rng2 As Range
    Dim rng3 As Range
    Dim rng4 As Range
    Dim cel As Range
    Dim d As Integer
    Dim i As Integer
[COLOR=#ff0000]    Set rng1 = Range("A1:H102")[/COLOR]
[COLOR=#ff0000]    Set rng2 = Range("B3:D102")[/COLOR]
[COLOR=#ff0000]    Set rng3 = Range("F3:H102")[/COLOR]
[COLOR=#ff0000]    Set rng4 = Range("A1:H1")[/COLOR]
[COLOR=#ff0000]    Set cel = Range("B3")[/COLOR]
    For d = 0 To 5000
        For i = 0 To 4
            If d = 0 And i = 0 Then i = 1
            If cel.Offset(103 * d, 9 * i) = "" Then
                rng4.ClearContents
                rng4.Value = "Chart " & i + d * 5
                rng1.Copy
                With rng1.Offset(103 * d, 9 * i)
                     .PasteSpecial xlPasteValues
                     .PasteSpecial xlPasteFormats
                End With
                rng4.ClearContents
                rng4.Value = "Main Chart"
                GoTo TheEnd
            End If
        Next i
    Next d
TheEnd:
Application.Calculation = xlCalculationAutomatic

this is just some old code but basically u only have ot change the rows which are red. and since im using offset + loops i dont need 30293019230192301923 cell references.

 

[gordonlyle]

Thank you both for your assitance.

I have used the Set & Dim on other macros but there are a lot of the refs are only used once so almost not worth doing.

I have a multitude of macros like the below.

Sub clearRail5()
'
' clearRail5 Macro
'
Application.ScreenUpdating = False
    Range("AQ17,AQ21").ClearContents
    Range("AQ25,AQ29,AQ33,AQ37").Formula = "=IF($AQ$13=" & """" & """" & "," & """" & """" & ",$AQ$13)"
    Range("AQ41").Formula = "=IF($AQ$13=" & """" & """" & "," & """" & """" & ",$AQ$9)"
    Range("AK19:AM19,AK23:AM23,AK27:AM27,AK35:AM35,AK39:AM39") = "Floating single"
    Range("AK31:AM31") = "Fixed single"
Application.ScreenUpdating = True
End Sub

Also, it would not update the .formula references either.

I think I am just going to have to be a little less lazy and type the things out.

Just one other question though, does the offset slow down the macros in any way?

Thank you again for your help.

 

[Ramballah]

Thank you both for your assitance.

I have used the Set & Dim on other macros but there are a lot of the refs are only used once so almost not worth doing.

I have a multitude of macros like the below.

Sub clearRail5()
'
' clearRail5 Macro
'
Application.ScreenUpdating = False
    Range("AQ17,AQ21").ClearContents
    Range("AQ25,AQ29,AQ33,AQ37").Formula = "=IF($AQ$13=" & """" & """" & "," & """" & """" & ",$AQ$13)"
    Range("AQ41").Formula = "=IF($AQ$13=" & """" & """" & "," & """" & """" & ",$AQ$9)"
    Range("AK19:AM19,AK23:AM23,AK27:AM27,AK35:AM35,AK39:AM39") = "Floating single"
    Range("AK31:AM31") = "Fixed single"
Application.ScreenUpdating = True
End Sub

Also, it would not update the .formula references either.

I think I am just going to have to be a little less lazy and type the things out.

Just one other question though, does the offset slow down the macros in any way?

Thank you again for your help.

Depends on how big your file is and how big ur code is. but it shouldnt as far as i know. i had my code loop accidentally without a break. it took just 20s to fill an entire worksheet from column a to the last and row 1 to the last :l

 

[Joe4]

Just one other question though, does the offset slow down the macros in any way?

I don't believe so.

Another thing you may want to consider is using named ranges in Excel. If you name your range on the worksheet, you can reference it in VBA code.
So, if you were to move a cetain section, if you update the named range to reflect that, you wouldn't need to change your VBA code at all.
And named ranges will autoamtically move as you insert/delete rows/columns.

To show a simple example of this, highlight cells A1:A4 and name the range "MyRange" (on the worksheet, not in VBA - if you do not know how to do this, see: https://www.contextures.com/xlNames01.html).

Now, run this code, and see what it returns:

Sub MyTest()

    MsgBox Range("MyRange").Address
    
End Sub

Now try inserting a whole column before column A, and run the code again. You can see that the named range now references B1:B4.
So, if you enlist this method, you won't ever need to update your VBA code, as long as your named ranges are referencing the correct range.

 

[Akuini]

I'm lazy too...
Maybe this would help, not about typing a range but about typing code easier.
I use a tool to help writing code easier, it's called Typingaid, it’s a freeware.
https://www.autohotkey.com/boards/viewtopic.php?t=5644

Basically here’s how it works:
You need to insert some entry in a dictionary.
Say, you insert entries like this in the dictionary:
xlup|r|set rng = Range("A1", Cells(Rows.Count, "A").End(xlUp))
xlup|r|set rng = Range("A1:C" & Cells(Rows.Count, "A").End(xlUp).Row)
xlup|r|n = Range("A" & Rows.count).End(xlUp).row

Then when you write code in the vbe, when you type ‘xlup’ , those entries will pop up like these:

then you can easily choose an entry & get it to your code.