Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
first sip of working with range in vba excel
Hi,
I have started learning vba for excel. Now, I feel fine with cells workarounds in a loop or else. But when it comes to a range, I can't find the reason why if I'm following the excel object model it's not working. Sub tryingtocopyrange() Dim wkb1 As Workbook 'copy from wkb1 Dim wkb2 As Workbook 'copy to wkb2 Dim wks1 As Worksheet 'source sheet for wkb1 Dim wks2 As Worksheet 'destination sheet for wkb2 Set wkb1 = Workbooks("source.xls") Set wkb2 = Workbooks("desti.xls") Set wks1 = wkb1.Worksheets("mysource") Set wks2 = wkb2.Worksheets("mydesti") 'A - the following is not working 'wks2.Range(Cells(1, 1), Cells(10, 3)) = wks1.Range(Cells(1, 1), Cells(10, 3)).Value 'B - this is working but i'd rather use the cell method as i find it easier to play with variables for indices in a loop 'wks2.Range("A1:C10") = wks1.Range("A1:C10").Value 'C - using the copy method, I should be able to copy a range and set one cell as a destination wks1.Range(Cells(1, 2), Cells(10, 2)).Copy Destination:=wks2.Cells(4, 1) End Sub This little step that makes my life hard as a non-programmer trying to code in vba, once over would take me to bigger steps such as copying range into vba arrays... Thanks, Pascal |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
first sip of working with range in vba excel
bpascal123 explained :
Hi, I have started learning vba for excel. Now, I feel fine with cells workarounds in a loop or else. But when it comes to a range, I can't find the reason why if I'm following the excel object model it's not working. Sub tryingtocopyrange() Dim wkb1 As Workbook 'copy from wkb1 Dim wkb2 As Workbook 'copy to wkb2 Dim wks1 As Worksheet 'source sheet for wkb1 Dim wks2 As Worksheet 'destination sheet for wkb2 Set wkb1 = Workbooks("source.xls") Set wkb2 = Workbooks("desti.xls") Set wks1 = wkb1.Worksheets("mysource") Set wks2 = wkb2.Worksheets("mydesti") 'A - the following is not working 'wks2.Range(Cells(1, 1), Cells(10, 3)) = wks1.Range(Cells(1, 1), Cells(10, 3)).Value 'B - this is working but i'd rather use the cell method as i find it easier to play with variables for indices in a loop 'wks2.Range("A1:C10") = wks1.Range("A1:C10").Value 'C - using the copy method, I should be able to copy a range and set one cell as a destination wks1.Range(Cells(1, 2), Cells(10, 2)).Copy Destination:=wks2.Cells(4, 1) End Sub This little step that makes my life hard as a non-programmer trying to code in vba, once over would take me to bigger steps such as copying range into vba arrays... Thanks, Pascal Pascal, What you need to understand is the concept of 'fully qualified' references. Consider this approach: Sub CopyRange() Dim wksSource As Worksheet, wksTarget As Worksheet Set wksSource = Workbooks("source.xls").Sheets("mysource") Set wksTarget = Workbooks("desti.xls").Sheets("mydesti") With wksSource .Range(.Cells(1, 2), .Cells(10, 2)).Copy _ Destination:=wksTarget.Cells(4, 1) End With 'OR wksTarget.Cells(4, 1) = _ wksSource.Range(wksSource.Cells(1, 2), wksSource.Cells(10, 2)) End Sub -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
first sip of working with range in vba excel
A better solution:
Sub CopyRange() Dim wksSource As Worksheet, wksTarget As Worksheet Set wksSource = Sheets("Sheet2") Set wksTarget = Sheets("Sheet1") With wksSource .Range(.Cells(1, 2), .Cells(10, 2)).Copy _ Destination:=wksTarget.Cells(4, 1) End With 'OR Dim v As Variant v = wksSource.Range(wksSource.Range(wksSource.Cells(1, 2), wksSource.Cells(10, 2)).Address) wksTarget.Range(wksTarget.Cells(4, 1).Address).Resize(UBound(v), 1) = v End Sub All are single line statements so watch word-wrapping. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
first sip of working with range in vba excel
"bpascal123" wrote in message
... Hi, I have started learning vba for excel. Now, I feel fine with cells workarounds in a loop or else. But when it comes to a range, I can't find the reason why if I'm following the excel object model it's not working. [ ] 'B - this is working but i'd rather use the cell method as i find it easier to play with variables for indices in a loop 'wks2.Range("A1:C10") = wks1.Range("A1:C10").Value [ ] GS answered your question "Why is this working but that is not?" (Using Object qualifiers - which you in fact had partly correct in your earlier question about aligning data in columns.) I'd like to comment on the differences between using cell indexing and the A1 reference method you referred to just now. You are absolutely correct that using Cell(row, column) indexing is much more suited to programmatic manipulation than the alphabetic based A1 style of referencing. I'm coming to the conclusion, however, that there are times when using the A1 reference results in more readable code. Consider: ColAin = .Transpose(Range("A1:A" & Cells(Rows.Count, _ "A").End(xlUp).Row)) compared with: With WorksheetFunction C1in = .Transpose(Range(Cells(1, 1), _ Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1))) End With or even: LastC1in = Cells(Rows.Count, 1).End(xlUp).Row With WorksheetFunction C1in = .Transpose(Range(Cells(1, 1), Cells(LastC1in, 1))) End With The major distinction there, I think, is the difference between A1 style notation (the Excel user interface default) and RC (Row, Column) notation that is more naturally suited to programmatic indexing (but not commonly seen in the user interface.) Conclusion: use the notation that makes the most sense to you for the task at hand. -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
first sip of working with range in vba excel
Of course, you'll need to replace
Set wksSource = Sheets("Sheet2") Set wksTarget = Sheets("Sheet1") with... Set wksSource = Workbooks("source.xls").Sheets("mysource") Set wksTarget = Workbooks("desti.xls").Sheets("mydesti") -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
first sip of working with range in vba excel
Hi,
I replaced wks2.Range(Cells(1, 1), Cells(10, 3)) = wks1.Range(Cells(1, 1), Cells(10, 3)).value with wks2.Range(wks2.Cells(1, 1), wks2.Cells(10, 1)) = wks1.Range(wks1.Cells(1, 1), wks1.Cells(10, 3)).Value Honestly, i haven't been able to see i needed to link the cell property in a range to a specific worksheet. Because to me, the range object is already linked to a specific worksheet then the cells property in the range object doesn't to be linked to a specific range on a worksheet thanks Pascal |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
first sip of working with range in vba excel
"bpascal123" wrote in message
... Hi, I replaced wks2.Range(Cells(1, 1), Cells(10, 3)) = wks1.Range(Cells(1, 1), Cells(10, 3)).value with wks2.Range(wks2.Cells(1, 1), wks2.Cells(10, 1)) = wks1.Range(wks1.Cells(1, 1), wks1.Cells(10, 3)).Value Honestly, i haven't been able to see i needed to link the cell property in a range to a specific worksheet. Because to me, the range object is already linked to a specific worksheet then the cells property in the range object doesn't to be linked to a specific range on a worksheet Pascal, does the built-in help make sense to you? I found that it took me quite a significant period of time before the object model and the distinctions between objects, properties and methods began to actually make sense to me. By "quite a significant period of time" memory tells me that means some months, not days or even a few weeks. From the VBE window enter [ range property ] in the help search box -- you should get a list of articles to choose from. Quoting from that help text: <qWhen used without an object qualifier, this property is a shortcut for ActiveSheet.Range (it returns a range from the active sheet; if the active sheet isn't a worksheet, the property fails).</q In other words, when you use Range (or Cell) in code without a qualifier, VBA uses the ActiveSheet object -- which may or may not be what you expect it to be. What you said above is an excellent illustration of why it is necessary to develop an understanding of the rules that govern the compiler. In this case, what seems to be "common sense" to you is in fact a violation of the rules that the complier follows. wks2.Range(Cells(1, 1), Cells(10, 3)) = wks1.Range(Cells(1, 1), Cells(10, 3)).value *cannot* work, because you are expecting the Cells property to return cells from two different worksheets ... but there is only one AcitveSheet (and it could easily be neither wks1 nor wks2!) One of the things that was difficult for me to grasp is that there are both Range properties and Range objects ... and they do entirely different things. Let's see if I can summarize: The Cells property returns a Range Object consisting of a single cell. The Range property (many objects have a Range property- for instance: Excel Application Window objects, Worksheet objects and Range objects to name three) also returns a Range object - which may contain one or many cells, included in one or many areas. VBA rules allow you to use Range and Cells with or without object qualifiers - but if you do not qualify (disambiguate) the default object is always the active sheet. HTH! -- Clif McIrvin (clare reads his mail with moe, nomail feeds the bit bucket :-) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Working with Range | Excel Programming | |||
Working with a range | Excel Discussion (Misc queries) | |||
Working with range | Excel Programming | |||
Macro working in Excel 2003; not working in Excel 2000 | Excel Programming | |||
Excel VBA-Error, range function not working?!?!? | Excel Programming |