Report

Copying function using VBA [Solved]

Ask a question Newmie2013 10Posts Monday March 21, 2016Registration date May 11, 2016 Last seen - Latest answer on May 10, 2016 02:14AM
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
See more 
Helpful
+0
moins plus
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
Newmie2013 10Posts Monday March 21, 2016Registration date May 11, 2016 Last seen - Mar 21, 2016 01:24PM
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
Reply
Add comment
Helpful
+0
moins plus
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

Newmie2013 10Posts Monday March 21, 2016Registration date May 11, 2016 Last seen - Mar 22, 2016 03:12PM
Hi Trowa!

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

Thanks a million!

Best Regards!

Newmie
Reply
Newmie2013 10Posts Monday March 21, 2016Registration date May 11, 2016 Last seen - May 10, 2016 02:14AM
HI Trowa,

I Need your Expertise again...

Can you help me?

Regards,

Newmie
Reply
Add comment

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.

Not a member yet?

sign-up, it takes less than a minute and it's free!