Next Blank Column

[RyanPJK]

Hi Guys,

I have a Excel 2003 spreadsheet, where I am trying to import some data from Sheet2 on to the next available Column (nCol) on Sheet1.

For example, I hit the "Import" button and the required data will paste itself into Column B7. The second time I hit the button, the data would go into Column C7 and so on.

Here is an extract with of area I'm haveing trouble with. It is part of a larger macro, where "var_TRN" and "var_MRN" is the data I need to bring from Sheet2 to Sheet1 (they have already been identified).

The area the where I need to say is the problem I'm stuck on but I can't seem to figure it out.

[FONT=Arial]Application.ScreenUpdating = False[/FONT]
 
[FONT=Arial]With Sheet1[/FONT]
 
[FONT=Arial]'*** nCol (next Column) should mean "nRow = Next Blank Column, Row 7". ***[/FONT]
[FONT=Arial]nRow = .Cells(Rows.Count, 7).End(xlToLeft).Column + 1+ 1[/FONT]
 
[FONT=Arial]End With[/FONT]
 
'*** I think there should be a line here to call nCol ***
'*** e.g. Range(nRow).Select *** or something... :s ***
 
[FONT=Arial]ActiveCell.Offset(0, 0) = var_TRN[/FONT]
[FONT=Arial]ActiveCell.Offset(1, 0) = var_MRN[/FONT]
 
[FONT=Arial]Application.ScreenUpdating = True[/FONT]
 
[FONT=Arial]End Sub[/FONT]

<!-- BEGIN TEMPLATE: bbcode_code --><STYLE>.alt2 font {font: 11px monospace !important;color: #333 !important;}</STYLE><!-- END TEMPLATE: bbcode_code -->I hope I've explained myself and you can see what I've driving at...

I'd really appreciate any input as I'm completely lost - and pretty sure I'm just missing something stupid! :s

Many Thanks,
Ryan

 

[MrKowz]

To find the last populated column in row 7, use:

Dim LC as Long
LC = Cells(7, Columns.Count).End(xlToLeft).Column

 

[VoG]

Try

With Sheet1
     
    '*** nCol (next Column) should mean "nRow = Next Blank Column, Row 7". ***
    ncol = .Cells(7, Columns.Count).End(xlToLeft).Column + 1
     
    End With
     
    '*** I think there should be a line here to call nCol ***
    '*** e.g. Range(nRow).Select *** or something... :s ***
     
    .Cells(7, ncol).Value = var_TRN
    .Cells(8, ncol) = var_MRN
End With

 

[RyanPJK]

Thanks very much for your quick responses guys, they were both great.

VoG, yours did precicely what I needed!

Thanks again,
Ryan