Previous Topic Next Topic

Change a range dynamically - VBA script.

Solved/Closed
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Feb 3, 2015 at 04:52 PM
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Feb 4, 2015 at 02:35 PM
Hi everyone.

I am stuck on a small problem with writing a Macro in Excel and would appreciate some help.

I have the following script:

For Each c In ActiveSheet.Range("A4:AL54,AM4:AN34").SpecialCells(xlCellTypeBlanks)
c.Select
ActiveSheet.Paste
pastes_left = pastes_left - 1
Exit For
Next c

This pastes a value from one sheet of my spreadsheet into vacant spaces in the specified range on a different sheet (the now active sheet). However, when the spaces in this range are full (so there is nowhere to paste the data), I need to select the same range, but on a different sheet and paste the data into vacant cells on that (different) sheet. The "overflow" sheet already exists with random spaces in the range required. The pastes_left variable counts down every paste so the script knows when to change pages (ie when pastes_left=0).

My question is ... how do I specify the new range(sheet) so that it fills all the spaces in sheet one and then continues to paste the remainder into sheet 2? I think Iwant the range in my script to be a variable so when pastes_left=0 the script says "Ok, now that range is full I want to go onto the next range."


I hope I have explained in a clear enough fasion and look forward to your comments.
Related:

2 responses

Answer 1 / 2
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Feb 3, 2015 at 06:46 PM
I think this is what you wanted. Not positive though.
It all seems a bit clunky to me. There must be a better way to do this.

Hopefully I have interpreted your spec correctly.


For testing I assigned a value of 20 for the number of Pastes to be done.
It will loop through all the blank cells in a sheet and paste the value into each of them until the number reaches zero when it will then reset the number of pastes and change to the next sheet.

What is supposed to happen if the number of pastes is greater then the number of blank cells in the sheet? Is that likely to ever happen?

If there are no cells in the range an error occurs and so it will exit the sub. 


Sub pasterange()
Dim xWs As Worksheet
Dim c As Range
Dim I as Integer

'*******************************
' Copy Value for Testing Only
Sheet3.Range("E1").Copy ' Pre-commit sheet
'*******************************

' This is required as it will cause an error if no empty cells are found
On Error GoTo Exit_Loop

' Start at the 2nd Sheet, assume 1st sheet is the Pre-Commit sheet
For i = 2 To Application.Sheets.Count
    
    pastes_left = 20 ' Set the number of pastes for the sheet
    
    Set xWs = Sheets(i)
    xWs.Activate
    
    For Each c In xWs.Range("A4:AL54,AM4:AN34").SpecialCells(xlCellTypeBlanks)
        xWs.Range(c.Address).PasteSpecial Paste:=xlPasteValues
        pastes_left = pastes_left - 1
        If pastes_left = 0 Then Exit For
    Next c
Next i

Exit_Loop:

'Clear the clipboard
Application.CutCopyMode = False

End Sub
1
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Feb 3, 2015 at 07:36 PM
Thanks Ray,

This is a mess - my description is awful!

I will look at your code tomorrow - its 12:30AM here - I put my bad description down to tiredness.

Your code looks good at first sight - but if I dont get on with it I will add another description tomorrow. (or soon after)

Thanks again
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Feb 4, 2015 at 02:35 PM
Thanks Ray,

This works (with a very slight modification to take the pastes_left from the existing script).

Now I have a new problem - The peramiters of the project have changed and I think I need to make this more modular in its approach bringing in sub routines as and when needed (a bit like "includes" on a php web site). I was hoping to do this as a linear script, but it seems other departments have heard of what Im trying to do and want to bolt more thins onto it. Great for learning, but I just want to get this out of the way!

I hate it when that happens!

At least you script will be part of the final thing :o)

Thanks again ... and please be prepared for more questions later.
0
Answer 2 / 2
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Feb 3, 2015 at 05:34 PM
As you probably know, as it stands the code you provided does not work.

Is this all the code there is?

Before I attempt to supply a solution I have a few of questions for you:

1. Where is the pastes_left value coming from?
What is the starting value?
Is this starting value different per sheet?
To clarify this value is reset to the starting value at the start of each
sheet. Correct?

2. What are you copying that you want to paste?
Is it a Cell value?
Is it a value that you have added to the clipboard via Ctrl-C?

3. Does this affect ALL sheets in the workbook?

Ray
0
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Feb 3, 2015 at 05:57 PM
Thanks Ray,

Yes - the code supplied is a nippet of the whole code, but I will try to answer your questions. A story fasion is perhaps the best way so I can explain what happens and where I am up to now.

Ok - a store of product types (stored as strings) is to be kept and depending on what they are they are stored on different sheets of an excel workbook. The user puts the products into sheet 1 and they get transfered to the correct sheets after a button is pressed. The reason for this is that in the past people have put the data into the wrong sheets. The first page gives a chance to check the numbers are correct before being committed.

The pastes_left is taken as a count of all the products that the user puts in.

When the sheet is full a second sheet is to be invoked so other users can do bits they need to - I dont know why its not all on one sheet, but thats the way it has to be ...

so the pastes_left needs to be the same as it was on the previous sheet (soe every paste is counted for and when the count gets to zero then the macro ends.

The value is a string that is copied to the clipboard.

No - the whole workbook is not affected - only the sheets that need to be updated.

Thanks in advance for any help you can give.
0

Similar discussions

Anu Script Manager 7 software for Windows 7
neelamnagaraja -
xpcman -

1 response
Ig stories from close friends to public
Darren -
zara -

31 responses