Setting a default cell value for blank cells in a range 1

[Walter349]

I am having a small, but irritating problem trying to extract data from one workbook into another, the extract is fine as far as it goes, if there is content in the source worksheet cell. No problem.

But if there is no data in the cell, then it causes the data being extracted into the destination sheet to become unsychronised.

I have attempted to correct this by setting a default cell content to be used if the source cell is blank. But while it works for a single cell range, it will not work for a multiple cell range.

The multiple cell range has contiguous and non-contiguous cells stated. ("E26,E28,E30,E32:40") etc.

The following is the code I have so far. The problem area is indicated. In the main macro there are 117 cells involved. I have cut it short for the sake of clarity here.

code
sub importWkRptData()

Dim X as Long, Z as Variant, Y as Variant
Dim Bk as workbook, Sh as worksheet, Sh1 as worksheet
Dim rng as range
Dim rng1 as range

set sh = workbooks("WRTest1a.xls").worksheets("Sheet1") 'Destination

application.screenupdating = false

'Get the fields to be used for extracting data.

z = application.getopenfilename(filefilter:="Excel Files (*.xls), *.xls", Multiselect:= true
if not isarray(z) then
msgbox "Nothing was selected"
exit sub
end if

For x = 1 to Ubound(z)

Set Bk = Workbooks.Open(z(x))


on error resume next
set sh1 = Bk.worksheets("Weekly report") ' The data source sheet in the source report

on error goto 0

If not sh1 is nothing then

set rng = sh1.range("C2")
Set rng1 = sh.cells(rows.count,1).end(xlUp) (2)
if rng = "" then
rng = "No site name entered"
rng1.copy
rng1.pastespecial xlvalues
else
rng.copy
rng1.pastespecial xlvalues
end if

set rng = sh1.range("C6")
Set rng1 = sh.cells(rows.count,2).end(xlUp) (2)
if rng = "" then
rng = "No Date entered"
rng1.copy
rng1.pastespecial xlvalues
else
rng.copy
rng1.pastespecial xlvalues
end if


set rng = sh1.range("E2")
Set rng1 = sh.cells(rows.count,3).end(xlUp) (2)
if rng = "" then
rng = "No FT name entered"
rng1.copy
rng1.pastespecial xlvalues
else
rng.copy
rng1.pastespecial xlvalues
end if
'==========Problem area===============================

set rng = sh1.range("E26,E28,E30,E32:40")
Set rng1 = sh.cells(rows.count,4).end(xlUp) (2)
if rng = "" then
rng = "--"
rng1.copy
rng1.pastespecial xlvalues, transpose := true

'======================================================
else
rng.copy
rng1.pastespecial xlvalues, transpose := true
end if

BK.CLOSE
next X
mSGBOX "The Data import is complete"

End sub
/code



'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

Hi,

How are you importing the data?

What columns of data are you importing?

When you say, ("E26,E28,E30,E32:40"), do you mean that either ROW 27 or 28 is entirely void of any data in column E AND the adjacent columns within the table?

Please answer all these questions.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Walter349]

How are you importing the data?
-The data is not being imported, the code above opens all the selected source files,in turn, which are laid out as a report, identical in all areas other than contents. Then copies the contents into the destination file, consolidating the information of many reports into one report.

What columns of data are you importing?
-In this example case column E.

When you say, ("E26,E28,E30,E32:40"), do you mean that either ROW 27 or 28 is entirely void of any data in column E AND the adjacent columns within the table?

- Only the cells ("E26,E28,E30,E32:40"), are copied, for this case, as stated there actually 117 of them in all. But lets keep this simple. The spcified cells in the source file, may, or may not have content in them. Any cells not listed above are not required. So Cell E27, E29, E31 never have data content and are not required.

As stated this does work, but I need to be able to fill the cells that are required, if they are blank, to stop the rows becoming out of sync.

I hope this answers everything

Please answer all these questions.

'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

Then I do not understand your statement, "But if there is no data in the cell, then it causes the data being extracted into the destination sheet to become unsychronised."

What does it mean to become "unsychronised?"

Maybe a concrete example.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Walter349]

Hi Skip.

If the first cell in the group E26 is empty, the in the next iteration, for the next report, the row is offset up one row.

Its 23:00 now, so will try and send something more concrete tomorrow as an example.

The first sections did this also for cells C2, C6 and E2, but I fixed that by putting a check in and ensuring the cell had a default content. But that does not seem to work for a range with more than one cell reference in the range

'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

Try this, as your original "range" syntax was incorrect...

    '==========Problem area===============================
    Set rng1 = Sh.Cells(Rows.Count, 4).End(xlUp)(2)
[b]
    Dim r As Range
    Set rng = Sh1.Union(Range("E26"), Range("E28"), Range("E30"), Range("E32:40"))
    For Each r In rng
        If r.Value = "" Then r.Value = "--"
    Next[/b]
    rng.Copy
    rng1.PasteSpecial xlValues, Transpose:=True

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Syntax error, sorry

Set rng = Union(Sh1.Range("E26"), Sh1.Range("E28"), Sh1.Range("E30"), Sh1.Range("E32:40"))

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[SkipVought]

Your problem is really with your logic.

You are using a technique that finds the LAST cell containing data in a column. That is not what you intend.

You know how many cells/rows you are copying. Therefore, you can, with certainty, locate the next place to paste, simply with a row counter. Does not matter if ALL the cells you copied had data or NONE had data.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[Walter349]

Skip,

Morning! at least it is here.

I tried that, and am getting an error dialog " Runtime error 1004, application defined or object defined error" when it is run as soon as it hits the new code area for column E. There does not seem to be an obvious cause.

The code stops at the first report, with the report open and from that I can see that all the relevant cells in column E, have been selected as the 'marque' is shown around them.

The data for cells C2, C6 & E2 are extracted into the consolidation report OK.

The reason that I was looking for the last row, was that this is an on going action. Every week I have to extract the reports sent in, 44 at the moment, and extract the data into a consolidation report, this will go on for the whole year.

Each report data extract goes on a new row against the site name,Date,FT (C2,C6, E2)for each report.

So each week data from another 44 reports to be added.





'If at first you don't succeed, then your hammer is below specifications'

 

[Walter349]

Skip,

Errhh! Forget my last. Found the problem, Chair to keyboard interface fault!

'If at first you don't succeed, then your hammer is below specifications'

 

[Walter349]

Skip,

new hitch, When I set 'rng' to comprise all the data fields to be extracted. Everytime it hits a new column, E,F,G,H,I & J, the data starts on a new row for each column of data, rather than continuing in the same row, which is what I expected to happen.

The data is all there and each time it starts to append in the correct column (4), but each column of data is put into a new row.

It is definately down to the different columns as I have tested it against each individual column of data and also a mix of columns.

'If at first you don't succeed, then your hammer is below specifications'

 

[SkipVought]

WHAT different column? You never stated or had code for putting stuff in a different column, did you?

post your code.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[PHV]

I'd try this:
rng1.PasteSpecial xlValues, Transpose:=[!]False[/!]

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Walter349]

Mea culpa,

Didn't think it was relevant at the time I was asking, for the question being asked, about setting up a default cell content.

I assumed that you could apply all the different columns into the same statement and have them extracted. So did not identify that as a problem at the time.

But I have resolved the issue.

Posting the code presents a problem as its on a different network. If can I will try and transfer it, but no great hopes on that.

Currently everything is extracted to the destination workbook as expected and the content split out onto three seperate worksheets all in the correct layout and sequence.

So its working fine.

Many thanks for the effort and advice on this. The resolution you provided did exactly what was wanted.




'If at first you don't succeed, then your hammer is below specifications'