Copying function using VBA

Solved/Closed
Report
Posts
10
Registration date
Monday March 21, 2016
Status
Member
Last seen
May 11, 2016
-
Posts
10
Registration date
Monday March 21, 2016
Status
Member
Last seen
May 11, 2016
-
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

Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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
Posts
10
Registration date
Monday March 21, 2016
Status
Member
Last seen
May 11, 2016

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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
490
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.
Posts
10
Registration date
Monday March 21, 2016
Status
Member
Last seen
May 11, 2016

Hi Trowa!

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

Thanks a million!

Best Regards!

Newmie
Posts
10
Registration date
Monday March 21, 2016
Status
Member
Last seen
May 11, 2016

HI Trowa,

I Need your Expertise again...

Can you help me?

Regards,

Newmie