Flexibility assign Cell value

[moonhk]

I am using Class member to store the data config. My Problem is that
when worksheet add/change/delete row. I need to reconfig it again. It
any flexibility way to do this ?

Option Explicit
Public Main As New clsMain
Public errStr As String
Public errFlag As Boolean
Public Raw As New clsConfigSinraw
Public Target As New clsConfigSinTarget
Public DNS As New clsDNSummary

Public Sub getConfig()
Dim loSheet As Worksheet
On Error Resume Next
errFlag = False
Set loSheet =
Application.Workbooks(Main.getVFile).Sheets(Main.getVmainNameGEN)
If loSheet Is Nothing Then
errFlag = True
errStr = errStr + ""
Exit Sub
End If
'~~return to normal error handling
On Error GoTo 0
DNS.SummarySh = loSheet.Range("B5").Value
DNS.AmountAddr = loSheet.Range("B6").Value
DNS.DNNAddr = loSheet.Range("B7").Value
DNS.MfgproAccAddr = loSheet.Range("B8").Value
DNS.MfgproCCAddr = loSheet.Range("B9").Value
DNS.CurrAmtAddr = loSheet.Range("B10").Value
DNS.SubTotalAddr = loSheet.Range("B11").Value
DNS.MarkupAddr = loSheet.Range("B12").Value
DNS.BT_RCTAddr = loSheet.Range("B13").Value
DNS.VAT_CSAddr = loSheet.Range("B14").Value
DNS.TotalAmtAddr = loSheet.Range("B15").Value
DNS.LinePropAddr = loSheet.Range("B16").Value
DNS.SupplierAddr = loSheet.Range("B17").Value
DNS.CurrencyAddr = loSheet.Range("B18").Value
DNS.EffectdateAddr = loSheet.Range("B19").Value
DNS.DateAddr = loSheet.Range("B20").Value
DNS.ProjectCodeAddr = loSheet.Range("B21").Value
DNS.DescriptionAddr = loSheet.Range("B22").Value


DNS.AmountHAddr = loSheet.Range("A6").Value
DNS.DNNHAddr = loSheet.Range("A7").Value
DNS.MfgproAccHAddr = loSheet.Range("A8").Value
DNS.MfgproCCHAddr = loSheet.Range("A9").Value
DNS.CurrAmtHAddr = loSheet.Range("A10").Value
DNS.SubTotalHAddr = loSheet.Range("A11").Value
DNS.MarkupHAddr = loSheet.Range("A12").Value
DNS.BT_RCTHAddr = loSheet.Range("A13").Value
DNS.VAT_CSHAddr = loSheet.Range("A14").Value
DNS.TotalAmtHAddr = loSheet.Range("A15").Value
DNS.LinePropHAddr = loSheet.Range("A16").Value
DNS.SupplierHAddr = loSheet.Range("A17").Value
DNS.CurrencyHAddr = loSheet.Range("A18").Value
DNS.EffectdateHAddr = loSheet.Range("A19").Value
DNS.DateHAddr = loSheet.Range("A20").Value
DNS.ProjectCodeHAddr = loSheet.Range("A21").Value
DNS.DescriptionHAddr = loSheet.Range("A22").Value
End Sub

 

[NickHK]

You can trap those action by modifying:
http://www.dailydoseofexcel.com/archives/2006/08/21/capture-deleted-rows/

NickHK

 

[moonhk]

Hi NickHK
It is not my case.

My problem is that if new row added in B6
Below code need to change
New Coding should be update as below.

How to hande this case ?

 

[NickHK]

Don't understand what you want then.
How do you know when a row is inserted/deleted then ?
You need some way to know that row 6 has been added, then adjust your value
to be +1 row.

NickHK

 

[moonhk]

Hi NickNK
When user add/delete row, Then the program will not work .
or When I add/delete row, Need to update the program again.

 

[NickHK]

Why not use named ranges, then it does not matter, assuming you do not allow
the user to delete them.
DNS.SummarySh = loSheet.Range("SummarySh").Value

NickHK

 

[moonhk]

I try before. But I have more than 300+ items need to named it. So, I
give up.