Beatrix
04-30-2012, 10:18 AM
Hi Everyone ,
I need to edit below script to count blank cells in dynamic range in column F only in Year6. F9 should display the result. At the moment Countblank formula calculates that but when data range changes it doesn't give the right value. I think OFFSET function would work in this case however I was wondering if there is a way to get it done by VBA script?:think:
sample files are attached.
Your help is appreciated.
Sub CopyDynamicDataRange()
Dim dataWB As Workbook, dataWS As Worksheet
Dim baseRng As Range, dataRng As Range
Set baseRng = ThisWorkbook.Worksheets("Year6").Range("A55")
Set dataWB = Workbooks.Open("S:\Assessment\CB\CBSYr6.xml")
With dataWB.Worksheets("Sheet1")
Set dataRng = .Range(.Range("A1"), .Range("A1").End(xlToRight))
Set dataRng = .Range(dataRng, dataRng.End(xlDown))
End With
Set baseRng = baseRng.Resize(dataRng.Rows.Count, dataRng.Columns.Count)
baseRng.Value = dataRng.Value
dataWB.Close False
End Sub
I need to edit below script to count blank cells in dynamic range in column F only in Year6. F9 should display the result. At the moment Countblank formula calculates that but when data range changes it doesn't give the right value. I think OFFSET function would work in this case however I was wondering if there is a way to get it done by VBA script?:think:
sample files are attached.
Your help is appreciated.
Sub CopyDynamicDataRange()
Dim dataWB As Workbook, dataWS As Worksheet
Dim baseRng As Range, dataRng As Range
Set baseRng = ThisWorkbook.Worksheets("Year6").Range("A55")
Set dataWB = Workbooks.Open("S:\Assessment\CB\CBSYr6.xml")
With dataWB.Worksheets("Sheet1")
Set dataRng = .Range(.Range("A1"), .Range("A1").End(xlToRight))
Set dataRng = .Range(dataRng, dataRng.End(xlDown))
End With
Set baseRng = baseRng.Resize(dataRng.Rows.Count, dataRng.Columns.Count)
baseRng.Value = dataRng.Value
dataWB.Close False
End Sub