Copying function using VBA

Solved/Closed
Newmie2013 Posts 10 Registration date Monday March 21, 2016 Status Member Last seen May 11, 2016 - Mar 21, 2016 at 12:15 PM
Newmie2013 Posts 10 Registration date Monday March 21, 2016 Status Member Last seen May 11, 2016 - May 10, 2016 at 02:14 AM
Hello,

I have a Problem that desperately Needs to be fixed:
I am trying to create a Participant List, and in order to Keep it "clean", am using the following Formula in A3:

=IF(B3="","",TRUNC(RANDBETWEEN(1,100)))

The Problem is that the numbers Keep changing.

My wish is this:

When B3 is changed, the Random number in B3 Needs to be copied and Pasted. And this only for the respective row where a Change took place.

Does that Make sense? I am new to VBA.... please help!

John

2 replies

TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 523
Mar 21, 2016 at 01:00 PM
Hi Newmie,

Your query is unclear to me.

When you enter something in A3, a random number between 1 and 100 will be entered in B3. If you want this number to be copy/pasted to C3, then type this in C3: =B3.

But I'm guessing this won't solve your query.

Please explain in greater detail.

Best regards,
Trowa
0
Newmie2013 Posts 10 Registration date Monday March 21, 2016 Status Member Last seen May 11, 2016
Mar 21, 2016 at 01:24 PM
Hi Trowa,


When I enter Data (eg. Mr. or Mrs.) in Column B (eg. B3) a random number is generated for the corresponding Cell in Column A (A3). But with each new data entry the Random numbers change. I need this to stop.

I need a Macro, (activated by cell changes in Column B) which takes the generated Random number in A (A3), copies it, and repastes it in the same cell (A3), effectively overwriting the formula. But this should only happen for the corresponding rows, not the entire column at once.

Is that more understandable?

Thanks!

Newmie
0
TrowaD Posts 2900 Registration date Sunday September 12, 2010 Status Moderator Last seen October 4, 2022 523
Mar 22, 2016 at 11:51 AM
Yes Newmie, I get it now.

Try the following code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim x As Integer
If Intersect(Target, Columns("B:B")) Is Nothing Then Exit Sub
x = Target.Offset(0, -1).Value
Target.Offset(0, -1) = x
End Sub


Right-click the sheets tab and select "View code". Paste the code in the big white field.

Best regards,
Trowa

Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
0
Newmie2013 Posts 10 Registration date Monday March 21, 2016 Status Member Last seen May 11, 2016
Mar 22, 2016 at 03:12 PM
Hi Trowa!

It works beautifully!! Thank you! Apparently i was way off the mark!!

Thanks a million!

Best Regards!

Newmie
0
Newmie2013 Posts 10 Registration date Monday March 21, 2016 Status Member Last seen May 11, 2016
May 10, 2016 at 02:14 AM
HI Trowa,

I Need your Expertise again...

Can you help me?

Regards,

Newmie
0