How to using Address in selected Range

[moonhk]

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
...

 

[FSt1]

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

 

[Rick Rothstein]

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.

 

[moonhk]

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

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