Copying function using VBA Solved/Closed

Question

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

TrowaD · Answer

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

TrowaD · Answer

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.

Copying function using VBA

2 responses

Similar discussions

Subscribe To Our Newsletter!