Hi All
I have a routine in an access module that writes an excel workbook with some very specific data, validation and formatting etc. Most of it works fine, but why doesn't this work?
Code:
1090 For inc = 0 To 23
'With objSheet.range("AM7:BB7").Offset(0, ColOffset)
1100 With objSheet.range("AD" & lngRowCount & ":AD" & lngRowCount + 1).Offset(0, inc)
1110 .MergeCells = True
1120 .WrapText = True
1130 End With
1140 Next
It works while 'inc' is zero (so column AD gets the merge/wrap), but not for any other values.
The 2nd line (commented out) is just there because I have used that syntax elsewhere, and it works :banghead:
'inc' is defined as an integer.
Hope someone can help - this is driving me nuts
Thanks
Les
Hi Cronk
Thanks for your suggestion. I tried the extra brackets, but sadly no joy
Hi Brian
Thanks also for your link: very interesting, but I don't think that's it in my case though, because I'm not trying to do anything with the merged cells afterwards so don't need to change the size of the range. Also, my syntax works fine with the other (commented out) line, where the range address is hard-coded rather than partially supplied as a parameter (lngRowCount) :banghead:
From the thread it does seem though that merge and offset are not happy companions
If you - or anyone else out there - has any other ideas I'd be really grateful.
There has to be a Select after the Offset to select the range as in
With objWS.Range("AD" & lngRowCount & ":AD" & (lngRowCount + 1)).Offset(0, inc).Select
Cronk
Now that looks interesting!
I won't be in the office until Monday (it's Sat night here - UK - where are you) so won't be able to test your syntax until then: but my intuition leads me to be hopeful
Cheers - I'll let you know.
Les
Cronk
Adding the extra brackets round (lngRowCount + 1)) had no effect.
Adding the .Select to the end of the statement gave me an "Object required" error
Am I going to have to have 23 separate statements, instead of the loop?
Hope for any more ideas
Thanks again.
Les
Not sure if this will help, but like eating chicken soup when one has a head-cold, "it couldn't hurt"
This is a "scratch pad code" I use to past and modify reports generated with Excel VBA. It is based on the number of records returned in a record set intMaxRecocord count. All of my Excel reports start at Row 5 (intRowPosition) so the maximum count will be the Number of Records Returned from the recordset + intRowPosition.
What this code does with the offset method. The Data is sorted by Name then by Transaction Date. Of course there may be other columns in-between.
The first time a Name appears - (i.e. the latest transaction) use the offset to Bold specific columns for that row.
Then, for the rest of the repeated names lighten the fonts for specific numbers of columns.
This formatting allows users to see the first use of a Name in bold.
While this uses Bold, in your case, just use the wrap text or other formatting.
This is a Conditional Formatting based on values. Yours may not need the IF statement.
The Resize method is very powerful. It can even be used against the original cell. Remember this if you ever need to change Excels format type. If your Query for example returns the Text value of 5 and you need Excel to change it to the Numeric value of 5. It is used for just about any formatting.
1340 ObjXL.Range("B" & intRowPos & ":B" & intMaxRecordCount + (intRowPos)).Select ' changed this to A for this report
'Debug.Print intRowPos & " start to end " & intMaxRecordCount
' Row B has names in order then transaction dates in order
' If Row B - bold the first x columns for a new name, lighten up the repeats
1350 For Each c In ObjXL.ActiveWindow.Selection
1360 If c.value <> c.Offset(-1, 0).value Then ' look at value below it
1370 c.Font.FontStyle = "Bold"
'c.EntireRow.Font.Bold = True
1380 c.Resize(, 2).Font.Bold = True ' bolds 2th columns out all Comments should be bold - try -1 for column A to be bold too
1390 Else
1400 'c.Font.ThemeColor = xlThemeColorLight1 'xlThemeColorDark1
' Win 7 turns white on line above and below - comment out for now
1410 'c.Font.TintAndShade = 0.349986267
'c.EntireRow.Font.ThemeColor = xlThemeColorLight1 'xlThemeColorDark1
'c.EntireRow.Font.TintAndShade = 0.349986267
'c.EntireRow.Font.TintAndShade = -0.249977111
'c.Resize(, 13).Font.ThemeColor = xlThemeColorDark1
'c.Resize(, 13).Font.TintAndShade = -0.549977111 ' lighten up repeat rows 5 columns out changed from -0.249977111 to make a little darker
' This next line should reach out and clear the second Stip as requested
'c.Resize(, 9).Font.Bold = True ' Reached out and bolded the 9th column ' this can reachout and bold every item in one column if needed
'c.Parent.Cells(c.ROW, 31).ClearContents ' removes Stip codes if it is a repeated record
1420 End If
1430 Next c
1440 ObjXL.Range("B" & intRowPos & ":B" & intMaxRecordCount + (intRowPos)).Select ' changed this to B for this report
1450 For Each c In ObjXL.ActiveWindow.Selection
1460 If c.value <> c.Offset(-1, 0).value Then
'c.Font.FontStyle = "Bold"
'c.EntireRow.Font.Bold = True
'c.Resize(, 1).Font.Bold = True ' bolds 2th columns out all Comments should be bold - try -1 for column A to be bold too
1470 c.Offset(, -1).Resize(, 1).Font.Bold = True ' This takes change in Pad Name - offsets left and bolds range
1480 Else
1510 End If
1520 Next c
1530 Set c = Nothing
Hi Rx
Thanks for your comprehensive post! I have to say that much of it was a bit beyond me , and I couldn't quite see whether what you've done applies to my situation. I do appreciate your help though.
Hi Cronk
Yep - I have
Code:
Set objSheet = objBook.Worksheets("All Staff")
at the head of the procedure. Not sure why .range hasn't been capitalized - but there are tons of others the same, and they all work
The complete routine is quite long - but I have uploaded, since you asked
The problem loop is at line 1090 (now commented out, and replaced with lines 1090 to 2040 .
It would be great if I could get this to work with the loop, as I have other similar issues in other modules that could benefit from the same approach.
Cheers
Les
As I currently don't have a machine I cannot test anything, this is being typed on an iPad, but I really do think that you need to use the resize function as in the link I posted and in RX s code. Another approach might be to index the range using Cells(row number, col number) but as I can't play with this I'm flying a bit blind.
As I don't use merge, or perhaps should say didn't, I cannot appreciate why merging ad then ae then af is different to just merging af.
Must have a play when I get my new machine.
OK - I will try again to fathom Rx's code to see if the resize function will do the trick. I am surprised that this is being suggested though, because I had thought that resize would just have changed the dimensions of cells, whereas I need to merge them, which is different - or is it
When you say about merging ad then ae then af being the same as just merging af, maybe I'm missing something: I want to merge AD3 with AD4, AE3 with AE4, then AF3 with AF4, etc. - so I end up with 3 pairs of merged cells (in fact I need 23 pairs ). I need to do this for successive pairs of rows (held by lngRowCount in my code), and I need to do it on two separate sheets in which the actual column addresses are different (hence ColOffset in my code).
Does this make sense - or am I on completely on the wrong track
It's probably me trying to get my head round it without being able to see it.
Resize is used to resize ranges which now that you have explained in English I now don't think applies to you. I think I would look to index my ranges using Cells(rownum, colnum)
OK, the code you posted helped. With out the data, this was written for column A, B going out from there. It runs in Excel if you put data in Row A9:G9. It will offset then select the cell below, merge the two and wraptext.
If Row 10 has data, it will error because merging two cells with data does that kind of thing.
You have a good grasp of object code to make this work for you.
Hope this helps
Code:
Public Sub test2()
Dim Inc As Integer
Dim ColOffset As Integer
Dim lngRowCount As Long
lngRowCount = 9
ColOffset = 3
Sheets("Sheet1").Range("A7").Select
For Inc = 0 To 5
With Excel.Application.Range("A7:B7").Offset(0, ColOffset)
With Excel.Application.Sheets("Sheet1").Range("A" & lngRowCount).Select
ActiveCell.Select
ActiveCell.Offset(0, Inc).Range("A1:A2").Select
1100 With Excel.Application.Selection
1110 .MergeCells = True
1120 .WrapText = True
1130 End With
End With
End With
Next
End Sub
Dont' get stuck on the "A1:A2" - that is just a relative cell for that range.
Tried to use your same variables with smaller values.
Regarding Resize, just an FYI
Code:
Sub test3() ' using resize in Excel
' select cell with data in the rectangular area of data
' The Current Region selects all of the data.
' Now add one extra row to that and select it for your next step
Dim NumRows As Long
Dim NumCols As Long
Dim Cr As Range
10 Set Cr = ActiveCell.CurrentRegion
20 NumRows = Cr.Rows.Count
30 NumCols = Cr.Columns.Count
40 Cr.Resize(NumRows + 1, NumCols + 1).Select
End Sub
Sub test4()
' say you wanted to union non-contiguous areas and select them
' the resize is more powerful
10 Union(ActiveCell.Resize(5, 2), ActiveCell.Offset(, 5).Resize(5, 2)).Select
End Sub
The Resize might be considered as shown when using offset.
Hope that helps.
Code:
Sub Test7()
Dim myRange As Range
' Set a range somehow for a placeholder
Set myRange = Range("A15:G16")
Range("G5:G8").Offset(0, -1).Resize(myRange.Cells.Count, 1).Select
' now F5 to F8 is selected
With Selection
.WrapText = True
End With
End Sub
Close. I put the example here that works in Excel, just substitute the object code for Excel object.
Added the Rows and Columns count so the example of Offset would be a little more self documented. The line 110 MyRange.Resize lends itself to some nice variable substitution.
Put this code in an Excel Module and step through it. Granted, it is more for demonstration than efficiency. Steps could be combined.
Code:
Public Sub test2()
' On Excel from A9:G9 - type in some long text in each
' this will highlight A9, select and merge the cell below it, then move to B9...
Dim Inc As Integer
Dim ColOffset As Integer
Dim lngRowCount As Long
10 lngRowCount = 9
20 ColOffset = 3
Dim numRows As Integer
Dim numCols As Integer
Dim MyRange As Range
30 Sheets("Sheet1").Range("A7").Select
40 For Inc = 0 To 5
50 With Excel.Application.Range("A7:B7").Offset(0, ColOffset)
60 With Excel.Application.Sheets("Sheet1").Range("A" & lngRowCount).Select
70 ActiveCell.Offset(0, Inc).Select
80 Set MyRange = ActiveCell
90 numRows = MyRange.Rows.Count
100 numCols = MyRange.Columns.Count
110 MyRange.Resize(numRows + 1, numCols).Select
' MyRange.Resize(numRows + 1, numCols + 1).Select ' example for you
120 With Excel.Application.Selection
130 .MergeCells = True
140 .WrapText = True
150 End With
160 End With
170 End With
180 Next
End Sub
Rx_
You said close but if your code is made specific it confirms what I wrote.
We know that the resize is to 2 rows and no change in the columns, which I think is achieved with the 0.
I don't use select and selection techniques unless forced to .
Les what the code is doing is selecting the cell on the top row by offsetting from AD and then expanding the range to include the cell on the next row. What you were trying to do was make the offset work on both rows.
