ActiveCell.Column 1

[PWD]

I'm just trying to set the references for the last blank cells in two columns and then fill down with data from:-
Range("F2").Select
ActiveCell.FormulaR1C1 = "z"
and
Range("G2").Select
ActiveCell.FormulaR1C1 = "Stockcheck"

by using the following:-

Selection.End(xlDown).Select
Column_Number = ActiveCell.Column + 1
Row_Number = ActiveCell.Row

Set SourceRange = Range("F2:G2")
Set fillRange = Range("F2:Column_Number & Row_Number")
SourceRange.AutoFill Destination:=fillRange

It's probably EXTREMELY simple - but so am I, so any help would be appreciated. Also this worksheet is from a .csv file downloaded from our mainframe so the actual cells aren't empty, although they appear to be blank, so the "Selection.End(xlDown).Select" statement doesn't actually find the last blank cell (unless I manually 'Clear Contents'). So should I just be running a loop down to find the last empty cell - although
Do Until Not IsEmpty(ActiveCell)
doesn't work either?

 

[waynerenaud]

Hi PWD, I cannot work out what you are trying to do.....
I figured you need "z" in cell F2 and "Stockcheck" in cell G2. What are you trying to do from there ?
Selection.End(xlDown).Select will select the next data entry in the current column, in your case column G, so if you have an entry in G3 it will select G3
Column_Number = ActiveCell.Column + 1 will return 8 as the current column is G (=7) + 1 = 8 which you cannot use in the range statement you have. Try and explain what you expect to see as the end result, I will help if I can.

 

[PWD]

Thanx. What I've got is columns F & G that have got some data but not in every row. I've sorted these columns in Ascending order so the 'blank' cells are in rows 2 thru ?? so I want to fill these with z & Stockcheck. As I said these 'blank' cells don't respond as being empty so it's tricky identifying them in the first place.

 

[SkipVought]

Hi,

Kinda hard to know how to test the values, but here's a possible solution.

with activesheet.usedrange
  for r = 2 to .row + .rows.count - 1
    with cells(r, "F")
      if .value = "" then  '???
        .value = "z"
      end if
    end with
    with cells(r, "G")
      if .value = "" then  '???
        .value = "Stockcheck"
      end if
    end with
  next
end with

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PWD]

Wow! "Damn fine coffee" er, I mean - code. Trouble now is trying to get XL to test out these so-called empty cells. The code only works when I've selected the cells and manually cleared the contents. Is there any way of finding out what XL thinks is there? (This seems to be similar to 'numbers' in a cell that appear to be formatted as a number but don't act as a number until you click in the formula bar and press 'Enter'. I think I'll post a new thread for that one.)

 

[SkipVought]

Not having your data, cant see what's there.

How 'bout seeing what is actually there??? Select a cell and run...

Sub WhatsInHere()
  For i = 1 To Len(ActiveCell.Value)
    s = s & ":" & Asc(Mid(ActiveCell.Value, i, 1))
  Next
  MsgBox s
End Sub

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PWD]

Right. That gives me :32 in each of these 'blank' cells. Any idea what that means?

 

[PWD]

Ah. If that 'Asc' stands for ASCII, then 32 = a space. Well, I'll be ............. If I click in the formula bar I can see the space. Well, I thought I'd done that - but obviously.........

Many thanks for the assistance.

Des.

 

[SkipVought]

Well, SOMETIMES what LOOKS like a space (ASCII 32) is some OTHER non-printable character.

So now you know what to test, right?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PWD]

Sure do!! Just another quickie, if I may. Some of the rows will have 'y' in column F. I can use what you've already given me to find them but how would I put appropriate text in column G? e.g I'd end up with:-
y Unpaid
I was thinking of some sort of offset, i.e.
If .Value = "y" Then
Offset(0, 1).Value = "Unpaid"

but it thinks that 'Offset' is a Sub or Function. It's quitin' time so I'll check back tomorrow.

Des.

 

[SkipVought]

with rng
      If .Value = "y" Then _
         .Offset(0, 1).Value = "Unpaid"
end with

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[PWD]

Once again I'm out of a jam. Thanks.

Des.