Macro to read (Random) indexed cell to modify list question.


Hi Everyone,

I have enclosed / Attached file - Excel worksheet (Marco enabled) –

Macro to read (Random) indexed cell to modify list question - 18th April 2021.xlsm

This List contains a List of words (of Indonesia / English vocabulary)

If you press F9 – the sheet will re-calculate and a new word – Indonesia = English appears.

This is great for learning a new language.

BUT after some time – I Master / (understand) the word displayed and now I wish to Delete this word from the list.

FYI – the list is indexed (see column range D20:D100,000 (infinity) so new words can be added to this list with new indexes.

I don't know anything about writing VBA for programming macros.

But I started to record a macro manually from START cell A1: which goes to the location D13 to copy the (dynamic) value in D13.

The Macro then selects column D, then opens control +F  Find, (Paste value from D13 & Find (dynamic) value in indexed number shown @D13).

The Macro then at that required  indexed number - move 1 cell to left (into column C) – enter "Delete" – then return to start A1.

That's ALL I want at the moment…!

But the PROBLEM is my macro - called EnterM – press (control +m) which must begin cell @ A1 always returns the SAME address found (474) created when I originally setup the macro.

What I NEED is for the macro to copy the DYNAMIC index value @D13 then find the position of that dynamic value in column D (indexes) then move one cell left - mark Delete in column C next to the that index value then back to A1.

Therefore as I learn more words and every time I wish to mark delete for new word shown in D13 then I press (control + m). Thereby I will be creating a column C of words to be deleted later. (To be done manually for the moment.)

So my QUESTION is can anyone show me how to edit my existing macro "EnterM" to work with dynamic cell D13 taking different value every time F9 is pressed & marking correct location in column C.

I only want the macro to run when I choose to press control + m.

Many thanks for your help & suggestions.

TIA. Leander…



Selected Answer

What a lovely idea you have there! Let me tell you right away that your intention is wholly possible. In the attached workbook I present another method, however. This is because once you start using VBA you would naturally choose between worksheet  and VBA functions and then you would start doing things differently.

Here is my idea. You don't need to use F9 anymore. Instead, you just click on D13 and the word changes. VBA is creating the random number.

And you don't need to plan on deleting any words. Instead, you just block them. If the random generator picks a blocked number it just goes right back and picks another. It's so fast, the user can't even notice. In that way you don't need to damage your database by deletions. You can unblock words again by simply deleting the word "blocked" in column C. 

To block a number you just double-click on a word that has come up (E13). In the attached workbook #2 in row 21 is blocked. You can also set blocks manually.  No need to worry about capitalization. The comparison is not case sensitive.

Here is the code. I have programmed it in simple terms so that you can easily follow and possibly modify it. Paste it to the code module of your Vocab List worksheet. That location is vital. The code won't work if pasted to another location. Refer to the attached workbook for guidance.

Option Explicit

' this constant is used in both procedures
Const FirstRow      As Long = 20    ' change to suit

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    ' 224

    Dim Rng     As Range            ' range to search in
    Dim Fnd     As Range            ' cell where a match was found

    With Target
        If .Address(0, 0) = "E13" Then
            Set Rng = Range(Cells(FirstRow, "D"), Cells(Rows.Count, "D").End(xlUp))
            Set Fnd = Rng.Find(.Offset(0, -1).Value, LookIn:=xlValues, LookAt:=xlWhole)
            If Not Fnd Is Nothing Then
                Fnd.Offset(0, -1).Value = "Blocked"
            End If
        End If
        Cancel = True
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' 224

    Dim LastRow         As Long                 ' last used row
    Dim RowID           As Long
    Dim i As Long

    With Target
        If .Address(0, 0) = "D13" Then
            LastRow = Cells(Rows.Count, "D").End(xlUp).Row

                ' pick a number between FirstRow and LastRow
                RowID = Int(2 + Rnd * (LastRow - FirstRow + 1))

                ' return to pick another number of the chosen one is blocked
            Loop While StrComp(Cells(RowID, "C").Value, "blocked", vbTextCompare) = 0

            .Value = Cells(RowID, "D").Value
            .Offset(0, 1).Select
        End If
    End With
End Sub

Note that my code doesn't save the workbook. Therefore, after the user doubl-clicked on a few words to block them they will be blocked only during the current session and will be unblocked automatically if the user closes the workbook without saving. Saving the workbook makes the blocks permanent.

Let me know if you still want your own idea implemented. I shall be glad to assist.



Many thanks for your reply & excellent coding. This is exactly what I need here. Really appreciate all your help. Best regards Lea...
Leander Rebanks (rep: 2) Apr 18, '21 at 11:27 pm
Add to Discussion

Answer the Question

You must create an account to use the forum. Create an Account or Login