Report

Copying function using VBA [Solved/Closed]

Ask a question 10Posts Monday March 21, 2016Registration date May 11, 2016 Last seen - Last answered on May 10, 2016 at 02:14 AM by Newmie2013
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
Helpful
+0
plus moins
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
10Posts Monday March 21, 2016Registration date May 11, 2016 Last seen - 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
Helpful
+0
plus moins
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

10Posts Monday March 21, 2016Registration date May 11, 2016 Last seen - 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
10Posts Monday March 21, 2016Registration date May 11, 2016 Last seen - May 10, 2016 at 02:14 AM
HI Trowa,

I Need your Expertise again...

Can you help me?

Regards,

Newmie

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!