Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to using Address in selected Range
Hi All
In below part of VBA, I am using offset to build k1 value. How to using range("B" & row_value) replace offset (0,1) ? Dim rngToSearch As Range, rngToCheck As Range Dim SchShtRange As String Dim SchShtName As String Dim k1 As String SchShtRange = "A1:A5" SchShtName = "RangeA" Set rngToSearch = Sheets(SchShtName).Range(SchShtRange) 'change to suit For Each c In rngToSearch If c.Value < "" Then k1 = c.Value + "|" & c.Offset(0,1).Value ... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to using Address in selected Range
hi
what problems are you having? i see some syntax problems and i am not sure switching from offset(0,1) to range("b"& row_value) will help? Regards FSt1 "moonhk" wrote: Hi All In below part of VBA, I am using offset to build k1 value. How to using range("B" & row_value) replace offset (0,1) ? Dim rngToSearch As Range, rngToCheck As Range Dim SchShtRange As String Dim SchShtName As String Dim k1 As String SchShtRange = "A1:A5" SchShtName = "RangeA" Set rngToSearch = Sheets(SchShtName).Range(SchShtRange) 'change to suit For Each c In rngToSearch If c.Value < "" Then k1 = c.Value + "|" & c.Offset(0,1).Value ... . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to using Address in selected Range
First off, the Range wouldn't be replacing the Offset property, it would be
replacing the c.Offest reference (assuming the "c" from your For..Each loop). Now, assuming the "c" from your For..Each loop is being used to establish the row number, I think you **think** you are looking for this... Range("B" & c.Row).Value or, alternately, like this... Cells(c.Row, "B").Value HOWEVER, doing this may (more than likely if your are not careful) end up referencing the wrong worksheet. The "c" in your For..Each loop automatically references the specified range on the worksheet you specified for it in the Set statement... on the other hand, the above alternates, as constructed, will reference the indicated cell on the ActiveSheet... the Range or Cells calls have no way of knowing you want to reference a different worksheet unless you tell it that. So, in your given For..Each loop, you would have to do what you ask this way... k1 = c.Value + "|" & Sheets(SchShtName).Range("B" & c.Row).Value or, alternately, like this... k1 = c.Value + "|" & Sheets(SchShtName).Cells(c.Row, "B").Value And, you would have to have the Sheet reference attached to the Range call each time you used it. Personally, I would continue using Offset myself. -- Rick (MVP - Excel) "moonhk" wrote in message ... Hi All In below part of VBA, I am using offset to build k1 value. How to using range("B" & row_value) replace offset (0,1) ? Dim rngToSearch As Range, rngToCheck As Range Dim SchShtRange As String Dim SchShtName As String Dim k1 As String SchShtRange = "A1:A5" SchShtName = "RangeA" Set rngToSearch = Sheets(SchShtName).Range(SchShtRange) 'change to suit For Each c In rngToSearch If c.Value < "" Then k1 = c.Value + "|" & c.Offset(0,1).Value ... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to using Address in selected Range
On Apr 3, 4:54*pm, FSt1 wrote:
hi what problems are you having? i see some syntax problems and i am not sure switching from offset(0,1) to range("b"& row_value) will help? Regards FSt1 "moonhk" wrote: Hi All In below part of VBA, I am using offset to build k1 value. How to using range("B" & row_value) *replace offset (0,1) ? * *Dim rngToSearch As Range, rngToCheck As Range * *Dim SchShtRange As String * *Dim *SchShtName As String * Dim k1 As String * SchShtRange = "A1:A5" * SchShtName = "RangeA" * * Set rngToSearch = Sheets(SchShtName).Range(SchShtRange) 'change to suit * *For Each c In rngToSearch * * * * If c.Value < "" Then * * * * *k1 = c.Value + "|" & *c.Offset(0,1).Value * * * * * * ... . I already define rngToSearch, how to using rngToSearch using .range(B & row_value ) ? It is because I want replace more then 20 columns. I want using range rather than offset Set rngToSearch = Sheets(SchShtName).Range(SchShtRange) 'change to Option Explicit '~~ Class Modules : clsSch Public schKey1 Public schKey2 Public schkey3 Sub Class_Initialize() schKey1 = 0 schKey2 = "B" schkey3 = 2 End Sub Option Explicit '~~ Class Modules : clsChk Public chKey1 Public chKey2 Public chKey3 Sub Class_Initialize() chKey1 = 0 chKey2 = 1 chKey3 = 2 End Sub Sub Range_Update() Dim objSch As New clsSch Dim objChk As New clsChk Dim rngToSearch As Range, rngToCheck As Range Dim c, d, sName1 As String, sName2 As String Dim found As Boolean Dim NewCol As Integer Dim k1 As String, k2 As String Dim ChkShtName As String, SchShtName As String Dim SchShtRange As String Dim ChkShtRange As String SchShtName = "RangeA" ChkShtName = "RangeB" SchShtRange = "A1:A5" ChkShtRange = "A" NewCol = 5 Set rngToSearch = Sheets(SchShtName).Range(SchShtRange) 'change to suit Set rngToCheck = Sheets(ChkShtName).Range("A1:" & ChkShtRange & NewCol) 'change to suit For Each c In rngToSearch If c.Value < "" Then k1 = c.Value + "|" & c.Offset(0, objSch.schKey2).Value + _ "|" + c.Offset(0, objSch.schkey3).Value found = False For Each d In rngToCheck If d.Value < "" Then k2 = d.Value + "|" & d.Offset(0, objChk.chKey2).Value + _ "|" + d.Offset(0, objChk.chKey3).Value If k1 = k2 Then 'MsgBox k1 found = True '~~ Other Value d.Offset(0, 3).Value = c.Offset(0, 3).Value Exit For End If End If Next d If found = False Then NewCol = NewCol + 1 Set rngToCheck = Sheets(ChkShtName).Range("A1:" & ChkShtRange & NewCol) '~~Create Record With Sheets(ChkShtName).Range(ChkShtRange & NewCol) '~~ Key .Value = c.Value .Offset(0, objChk.chKey2) = c.Offset(0, objSch.schKey2).Value .Offset(0, objChk.chKey3) = c.Offset(0, objSch.schkey3).Value '~~ Other Value End With End If End If Next c End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Address of selected range | Excel Programming | |||
Lower right cell address in a selected range - Excel 2007 | Excel Programming | |||
Get selected cell name/address | Excel Programming | |||
How do I convert a selected Cell address in a Range to Values? | Excel Programming | |||
How do you get a Selected Range address into a variable? | Excel Programming |