Posts by PieterGrove

I have created a command button to randomize a series of numbers. No issues. I am looking for a code that auto randomizes these numbers (once only) on a specific day of the month.

Code is as follows:

Private Sub CommandButton1_Click()


Dim size As Long, i As Long
Dim myArray() As Long, randomIndex As Long
Dim temp As Long
size = 80


ReDim myArray(1 To 80)
For i = 1 To 80
    myArray(i) = i
Next i


For i = 1 To size
    Randomize
    randomIndex = Int(Rnd() * size) + 1
    temp = myArray(randomIndex)
    myArray(randomIndex) = myArray(i)
    myArray(i) = temp
Next i


Range("E1").Resize(size, 1) = Application.Transpose(myArray)




size9 = 80


ReDim myArray9(1 To 80)
For i = 1 To 80
    myArray9(i) = i
Next i


For i = 1 To size9
    Randomize
    randomIndex9 = Int(Rnd() * size9) + 1
    temp9 = myArray9(randomIndex9)
    myArray9(randomIndex9) = myArray9(i)
    myArray9(i) = temp9
Next i


Range("E81").Resize(size9, 1) = Application.Transpose(myArray9)
End Sub

Awesome, thanks for the help.
Much appreciated.

Hi vcoolio,

I get the following error message:
Compile error: Statement invalid outside Type block and this line in the code a As Long, lr2 As Long, lr3 As Long highlighted.

Hi vcoolio,
Thanks for your reply, but I think I may have been a bit vague. Sorry.
Here Goes: In Sheet 1 Column A I have dropdown boxes created by data validation referring to Sheets 5 and 6. The dropdown box related to sheet 5 auto generates a comment box based on data in columns B, C,D,E in sheet5 . I would like the dropdown box related to sheet 6 to also auto generate a comment box based on data in sheet 6. Hope this makes sense. Please find attached example

I have the following code as listed below, no issues.

[FONT=Calibri][SIZE=12px][COLOR=#000000]Private Sub Worksheet_Change(ByVal Target As Range)[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]Dim sh2 As Worksheet, a As Long, lr2 As Long[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]Set sh2 = Sheets("Sheet 5")[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]lr2 = sh2.Cells(Rows.Count, 1).End(xlUp).Row[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]If WorksheetFunction.CountIf(sh2.Range("A1:A" & lr2), Cells(Target.Row, Target.Column).Value) <> 0 Then[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]Select Case Target.Column[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]Case 1, 2, 3, 4, 5, 6, 7[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]a = sh2.Columns(1).Find(Target.Value, , , 1).Row[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]With Cells(Target.Row, Target.Column)[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]If Not .Comment Is Nothing Then .Comment.Delete[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000].AddComment[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000].Comment.Text Text:=sh2.Cells(a, 2).Value & vbLf & _[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]sh2.Cells(a, 3).Value & vbLf & _[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]sh2.Cells(a, 4).Value & vbLf & _[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]sh2.Cells(a, 5).Value & vbLf & _[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]sh2.Cells(a, 6).Value & vbLf & _[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]sh2.Cells(a, 7).Value[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]End With[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]Case Else[/COLOR][/SIZE][/FONT]


[FONT=Calibri][SIZE=12px][COLOR=#000000]End Select[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=12px][COLOR=#000000]End If[/COLOR][/SIZE][/FONT]
[FONT=Calibri][SIZE=12px][COLOR=#000000]End Sub

[/COLOR][/SIZE][/FONT]

[SIZE=13px]Problem is I want this code to refer to more than one sheet, for example sheet 5 and sheet 6. Any Suggestions?[/SIZE]

Awesome, thanks you have been a great help.

Ok, can you please add sample?.

Perfect thanks. Looking at the numbers in Columns B to J, I am looking for a code that would randomize these numbers on the first day of the month. Can this be done?

Hi there,

I have a basic data sheet 1 with rows numbered from 1-31(Column A). Column A represents days of the month and have relevant data in Columns B to J
Would like to have number 1 with relevant data highlight on the first day of the month, Number 2 with relevant data highlight on the 2nd day of the month and so on. Also then have the highlighted row with data show on Sheet 2. Any suggestions please?

Done, thanks.

Awesome, working !!!
Thanks for all your help, much appreciated.

Thanks for all your help jolivanes.

Double click issue solved, just thought it would be handy not to populate a comment if cell is double clicked by accident
When I type a name not related to that of the data on sheet2 I still get an error message. On sheet 1 there will be the odd occasion where names are typed in that are not related to those on sheet2.

Awesome thanks,

All columns are working,
However when I double click the empty cells in these ranges it populates comment boxes without any data and when typing a name different to the data on sheet 2 I get an error message. Can this be prevented?

Once again thanks for your help, really appreciate this. Problem is that when I double click in A1 (Sheet1) it generates an empty comment box without relating to the text in sheet 2, also how would I extend the range in sheet 1 from columns A to H?

Thanks for your help.
Would it be possible to change the code to show a cell comment and not a message box?

I saw a previous thread on this topic but lost data on my computer and for the life of me can’t find it. So here goes:

I have a workbook with 2 sheets.

Sheet 2 has four columns named as follows: ( A) Customer Name, (B) Address, (C)Telephone Number and (D) Mobile Number.

I would like to type a customer name in sheet 1, column A listed in Sheet 2 under column (A) and then have the cell in sheet one auto populate a comment box with the relevant address, telephone number and mobile number.

Please help.

Thanks for all the help, the codes unfortunately did not work. However i managed to solve the issue by changing ranges to "B" in the userform inittialize event. Once again thanks.

Thanks Logit,
Have tried with both lines, get this errors message: "Object variable or With block variable not set". Deleted the first line and code updates the last row. however when opening the form again the form shows the 10th last row on my sheet and not the last row. Hope this makes sense?

Thanks for your guidance Alan, apologies for this.

I have a userform to update the last row. Code works when updating the last row once, problem is once the last row has been updated when opening the form it now jumps to the second last row. How can i adjust my code to prevent this from happening. I would like to be able to edit or update the last row multiple times, but only the last row and nothing else. Any help would be much appreciated.
Cross posted - https://www.mrexcel.com/forum/…last-row.html#post4949047

PubliclLastRow As Long, lCurrentRow As Long




Private Sub CommandButton1_Click()


Cells(lCurrentRow, 1) = TextBox1.Text
Cells(lCurrentRow, 2) = TextBox2.Text
Cells(lCurrentRow, 3) = TextBox3.Text
Cells(lCurrentRow, 4) = ComboBox1.Text
Cells(lCurrentRow, 5) = TextBox5.Text
Cells(lCurrentRow, 6) = TextBox6.Text
Cells(lCurrentRow, 7) = TextBox7.Text
Cells(lCurrentRow, 8) = TextBox8.Text
Cells(lCurrentRow, 9) = TextBox9.Text


Unload Me


End Sub

Private Sub UserForm_Initialize()
lLastRow = Range("A" & Rows.Count).End(xlUp).Row
Range("A" & lLastRow).Select
lCurrentRow = ActiveCell.Row




TextBox1 = Cells(lCurrentRow, 1)
TextBox2 = Cells(lCurrentRow, 2)
TextBox3 = Cells(lCurrentRow, 3)
ComboBox1 = Cells(lCurrentRow, 4)
TextBox5 = Cells(lCurrentRow, 5)
TextBox6 = Cells(lCurrentRow, 6)
TextBox7 = Cells(lCurrentRow, 7)
TextBox8 = Cells(lCurrentRow, 8)
TextBox9 = Cells(lCurrentRow, 9)








End Sub