If statements and VBA improvements

I'm new to VBA and need your assistance !

I have the following macro that fetches specific column data from one workbook to another. In short: VBA allows you to select the location of the sournce file "ImportFrom", opens it, selects specific columns and pastes them in to pre-defined columns in the target workbook: "PasteTo".

Actual code is attached as txt file.


The copy/paste part works just fine, however I'm having problems with the "IF" statement towards the end. The data source I use has a lot of values that come up as (Null) instead of 0. I want to tell excel to change the string (Null) to the number 0, for every cell in column N. I tried using "IF" and "Like" functions but with no luck.

For Each cell In Range("N2:N1500")
If cell.Value Like "*(Null)*" Then
cell.Value = "0"
Else
Exit Sub



Another thing I would like to improve is the copy/paste ranges. Intead of having copy Range(J1:J1500) and want excel to use dynamic ranges; i.e. take Range(J1:to whatever the last used cell in this columns is).

This was my initial idea:

wsImportFrom.Range("A2", Cells(.Rows.Count, .Columns.Count)).Copy
wsPasteTo.Range("J2", Cells(.Rows.Count, .Columns.Count)).PasteSpecial Paste:=xlPasteValues, _
operation:=xlPasteSpecialOperationNone, SkipBlanks:=False, Transpose:=False _


In addition, I want to make the code more dynamic and tell VBA to perform copy/paste on the basis of column headers, rather than pre-defined ranges (column J to E and so forth). For instance: I have a column called "Sales" in the source file (ImportFrom) and I want excel to take all entries and paste them in "Sales" located in the destination file (PasteTo).

This is the code I use in another file, which does exactly that:

Sheets("BIR").Select

desc = WorksheetFunction.Match("Contract num", Rows("1:1"), 0)
qty = WorksheetFunction.Match("Quantity", Rows("1:1"), 0)

Sheets("BIR").Columns(desc).Copy Destination:=Sheets("DATA").Range("A1")
Sheets("BIR").Columns(qty).Copy Destination:=Sheets("DATA").Range("B1")

On this instance I copy and paste data that is located in the same workbook. However, I don't know how to tell excel - take data from whatever file is selected via the pop-up window; open it; search for column "Sales"; copy all entries' now find column "Sales" in the destination workbook and paste values there.


I know this is a lot to ask for, but any help will be most appreciated !

Part 1
Use ISNULL to check

Please Login or Register  to view this content.