Previous Topic Next Topic

A strange Range question ...

Solved/Closed
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Feb 7, 2015 at 04:57 PM
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 - Mar 11, 2015 at 11:44 PM
Hi guys

Im back with a new twist on one of my old problems. I had help earlier and one of you kind people hellped me out with a solution (thanks again Ray), but you thought it clumpy and might be able to be done better. I might have thought of the better solution, but dont know how to impliment it if it is a valid idea....

Heres what I have .... (Ray, you may remember it from earlier) 

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



Now without reinventing the wheel (remember this has been sort of solved already and Im just trying to improve what is there already) my question ...

My problem is that when spaces on the active sheet (found on the first line of this code (and counted earlier in my coding)) are less than the pastes_left variable I need to select another sheet and do some pasting there. How about this workaround ... On the first line where I declare the range can I select a range that includes both sheets. I sort of expect the first line to look like this: 

For Each c In Range("Sheet4!A4:AL54,AM4:AN34,Sheet5!A4:AL54,AM4:AN34").SpecialCells(xlCellTypeBlanks)


But this isnt working for me. Is what I want possible, and if it is ... how?

Also, how do I make it look top to bottom and not left to right?

Thank you all for the help so far, and I promise that I will move on from this topic ... one day!

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Mar 5, 2015 at 11:19 AM
Hi Brian,

So you want to fill out empty cells in a sheet and once those have been filled, then move on to the next sheet. Right?

Are we talking about sheet 4 and 5 or are there more sheets involved?
Are the range always A4:AL54 and AM4:AN4 or are there more/different/dynamic ranges?
What needs to be put in the empty cells?

A sample of your sheet(s) layout would help to see what needs to be dealt with. Also consider uploading your workbook (careful with sensitive information) using a file sharing site like www.speedyshare.com or ge.tt and posting back the download link.

Best regards,
Trowa
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Mar 5, 2015 at 02:20 PM
Thanks TrowaD,

Your summation in your first question is correct --- I do want to fill out empty cells in a sheet and once those have been filled, then move on to the next sheet.

Question 2 - There are 3 sheets possible to paste the data into. However sheet 6 is a dual purpose (same tasks to be performed, but I want to try and reserve this sheet as much as possible). Thus, if a user has to do this manually, then that is a possible option.

Question 3 - Yes - the range to paste something in is always A4:AL54 and AM4:AN4 on every sheet.

What is pasted into the empty cells - The user puts data into sheet 1 in a list (cells D4 - Dx (where x is a variable depending on how much data is to be added to sheet 1)). This gives him/her the option to check the data (sheet 1) before committing it to the sheets 4 and 5 (and possibly 6 if I cant get round the reservation thing I want to do) by pressing the "Start Macro" button. (Eventually on this sheet (sheet 1) will be pasted the contents of the cell next to the freshly pasted data, but that is another story! ... and I hope easy!)

Unfortunately, due to the companies sensitivity about data control I cannot at the moment upload a copy of the spreadsheet, but if you really need it I can adapt it to remove the sensitive data. You would also be able to see and laugh at the code I have already done - which I really dont mind you seeing at all. Let me know if you want to see this still

Thank you so much for looking at this and hopefully helping.
Answer 2 / 2
RayH Posts 122 Registration date Tuesday August 31, 2010 Status Contributor Last seen June 20, 2016 26
Mar 5, 2015 at 12:20 PM
Brian,

I do remember this.
I cant remember if thought the solution we ended up with or the reason it had to be done that way was clumpy/clunky though.

The multiple sheet range is not possible unfortunately using that command. Good idea though.
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149
Mar 5, 2015 at 01:51 PM
Thanks Ray,

I did suspect that, but wasnt sure. I am about to respond to TrowaD's questions ... just in case he has an ace up his sleeve and can trump your answer ... I do hope so!

Thanks again for your response.
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555 > BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021
Mar 10, 2015 at 01:09 PM
Hi Brian,

This is what I got for you: 
Sub RunMe()
Dim EmptyCellsSh4, EmptyCellsSh5, EmptyCellsSh6, RowIndex, lRow As Integer

lRow = Sheets("Sheet1").Range("D" & Rows.Count).End(xlUp).Row
EmptyCellsSh4 = Application.WorksheetFunction.CountIf(Sheets("Sheet4").Range("A4:AL54"), vbNullString)
EmptyCellsSh4 = EmptyCellsSh4 + Application.WorksheetFunction.CountIf(Sheets("Sheet4").Range("AM4:AN4"), vbNullString)
EmptyCellsSh5 = Application.WorksheetFunction.CountIf(Sheets("Sheet5").Range("A4:AL54"), vbNullString)
EmptyCellsSh5 = EmptyCellsSh5 + Application.WorksheetFunction.CountIf(Sheets("Sheet5").Range("AM4:AN4"), vbNullString)
EmptyCellsSh6 = Application.WorksheetFunction.CountIf(Sheets("Sheet6").Range("A4:AL54"), vbNullString)
EmptyCellsSh6 = EmptyCellsSh6 + Application.WorksheetFunction.CountIf(Sheets("Sheet6").Range("AM4:AN4"), vbNullString)
RowIndex = 3

Sheets("Sheet4").Select
For Each cell In Range("A4:AL54,AM4:AN4").SpecialCells(xlCellTypeBlanks)
    RowIndex = RowIndex + 1
    cell.Value = Sheets("Sheet1").Range("D" & RowIndex)
    EmptyCellsSh4 = EmptyCellsSh4 - 1
    If RowIndex = lRow Then Exit Sub
    If EmptyCellsSh4 = 0 Then Exit For
Next cell
        
Sheets("Sheet5").Select
For Each cell In Range("A4:AL54,AM4:AN4").SpecialCells(xlCellTypeBlanks)
    RowIndex = RowIndex + 1
    cell.Value = Sheets("Sheet1").Range("D" & RowIndex)
    EmptyCellsSh5 = EmptyCellsSh5 - 1
    If RowIndex = lRow Then Exit Sub
    If EmptyCellsSh5 = 0 Then Exit For
Next cell

Sheets("Sheet6").Select
For Each cell In Range("A4:AL54,AM4:AN4").SpecialCells(xlCellTypeBlanks)
    RowIndex = RowIndex + 1
    cell.Value = Sheets("Sheet1").Range("D" & RowIndex)
    EmptyCellsSh6 = EmptyCellsSh6 - 1
    If RowIndex = lRow Then Exit Sub
    If EmptyCellsSh6 = 0 Then
        MsgBox "There are no empty cells left to fill."
        Exit Sub
    End If
Next cell
End Sub


First Excel counts the empty cells, then fills them. When there are no empty cells left, move on to the next specified sheet. When there are no empty cells left then a message box will appear, otherwise all the data from sheet1 (D4:Dx) has been pasted.

Best regards,
Trowa
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149 > TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022
Mar 10, 2015 at 03:02 PM
Wow - Thanks Trowa.

That was much more than I expected!

I will try and merge this with the code I have after I look and see exactly how you did it so I know what (silly) code I have and can replace it with yours.

Its been some time since I looked at this so it will probably take me a day or two to decipher what I have (again (I hate being new to coding!)) and try this, so it might take a few days to get back to and tell you how it went.

Thank you so much.

Brian.
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149 > BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021
Mar 11, 2015 at 06:14 PM
Well, I looked ...

The good news is that this code replaces a lot of the bad code I had already got. I knew it needed revising, but thought it best to get it working.

Now the bad news ...

Unfortunately, it throws an error on line 4 of your code. I am sure this is because the contents of the cell on sheet 1 (the source cell that needs to be copied to the other sheets) is not just numbers, so I think it is the part in line 2 of your code that declares "lRow As Integer". I have tried "lRow As String" but it throws the same error. I didnt think to make the sourse cell to just number so I might be barking up the wrong tree here. I will try this tonight and report back my findings About 2 hours from now. I will also add what the error is - im not in front of the work right now so cant add it at the moment.
BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021 149 > BrianGreen Posts 1005 Registration date Saturday January 17, 2015 Status Moderator Last seen September 30, 2021
Mar 11, 2015 at 11:44 PM
Ok Trowa - I can say withoutb doubt that your solution works with a small issue which I will try to fix on my own.

It seems the issue I was experiencing earlier was due to some scripting I had done earlier in the macro. I tried yours without my code and it works almost flawlessly.

The issue I have now is that if a sheet is full befor the macro is run, then there is a "Run-time error '1004' - No cells were found." I will tru to fix this myself and post my ammendment here, but I think my question has been fully answered here, so I will mark this one as "Solved".

Thank you so much for your help Trowa. I really do appreciate it :^)

Similar discussions

Keyboard troubles
jimbobjoe -
car -

38 responses
My account locked connect to gemail
Karthik -
winko -

2 responses
Excel Macro/Code help: Creating sheet for each name in column of another sheet
excelnovice2019 -
vcoolio -

21 responses
Copy range data in a column to new column.
anthonyishere -
anthonyishere -

6 responses
Flash drive files replaced with strange symbols and numbers
pboo -
Ambucias -

1 response