Copy down adjacent formuale using VBA (multiple columns)

**lucasreece** · 08-19-2010, 04:53 PM

Hi,

Looking for some help with the attached spreadsheet please...

What I'd like to do is when someone enters a value in column A, then copy down the formulae in columns C, E and H. So for row 2, when you enter a value, I need the formuale to copied down from C1, E1 and H1 to C2, E2 and H2 respectively (even if a row is missed e.g. blank row 3 and then value entered into A4, copy down formulae). Hope that makes sense.

The worksheet is protected to protect the formulae from being removed or changed so when someone enters a value in column A, the sheet needs to be unprotected (password is 'test'), formulae copied down and then sheet re-protected with the same password.

I can do this to a point and the attached file copies column C formula but not E or H. Please help. Also, if a line is missed it doesn't copy ANY formula. Again please looking for help with this.

Note: VBA code runs when worksheet is changed.

Many thanks.

Lucas.

test.xls

**pixifaery** · 08-19-2010, 06:56 PM

Hi Lucasreece

I've attached a spreadsheet which contains formulas (see sheet2). Rather than using VBA, I've locked the columns containing the formulas (format, cells, protection) and passworded the file with 'test'. This will allow the users to select columns in those cells but not to change them.

Is that what you wanted?

Regards
P

**mojo249** · 08-19-2010, 06:56 PM

Lucas

You need to be careful when you have a macro in the Worksheet_Change event which changes the sheet - because your macro will trigger itself so you get a looping effect. If you remove the error handler in your code you'll notice that it debugs on the line:

Please Login or Register  to view this content.

This is your macro's calling of itself, not your own calling of it.

To get around this, you need to stop the macro from running after you've requested it - I've declared a public string (varCancelChange) in module 1 and added the extra lines. Perhaps others have a better way of solving the problem....

Dion

Please Login or Register  to view this content.

**lucasreece** · 08-21-2010, 06:13 AM

Thanks for your help and advice.

I ended up reordering the code in the worksheet change event and it now works...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r As Long
r = Target.Row
Select Case Target.Column
Case Is = 1
Call UnprotectSheet
Range("C" & r - 1).Copy Range("C" & r)
Range("E" & r - 1).Copy Range("E" & r)
Range("H" & r - 1).Copy Range("H" & r)
Call ProtectSheet
End Select
End Sub

Thanks.

**Cutter** · 08-21-2010, 11:34 AM

You mentioned in your original post that you wanted the formulas "copied down even if a row is missed". That being the case, your solution will not work for a row that follows a skipped entry in Col A because nothing is in the preceding row to be copied.

You would need to change:

Please Login or Register  to view this content.

To:

Please Login or Register  to view this content.

And the same for E and H.

Copy down adjacent formuale using VBA (multiple columns)

LinkBack

Thread Tools

Rate This Thread

Display

Copy down adjacent formuale using VBA (multiple columns)

Re: Copy down adjacent formuale using VBA (multiple columns)

Re: Copy down adjacent formuale using VBA (multiple columns)

Re: Copy down adjacent formuale using VBA (multiple columns)

Re: Copy down adjacent formuale using VBA (multiple columns)

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions