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