Selecting a Range(F>CurrentRow:M>CurrentRow) using Offset?

[newcoder]

Hello -
I have the following code working, but I am having to select each cell F-M individually. I want to be able to compress this code.

Select the current row columns F-M then set the NumberFormat and HorizontalAlignment...

I know this code is probably a laughing matter to some, but I don't know how to select the current row in this code...
I have tried all combinations of Range, Offset, etc.

Again the code works, but I would like to stream line the first 16 lines because I need to format other items in the sheet and I know this is not the most efficient way.

Thanks as always in advance....

                  wsDest.Range("F" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
                  wsDest.Range("F" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
                  wsDest.Range("G" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
                  wsDest.Range("G" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
                  wsDest.Range("H" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
                  wsDest.Range("H" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
                  wsDest.Range("I" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
                  wsDest.Range("I" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
                  wsDest.Range("J" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
                  wsDest.Range("J" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
                  wsDest.Range("K" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
                  wsDest.Range("K" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
                  wsDest.Range("L" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
                  wsDest.Range("L" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
                  wsDest.Range("M" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
                  wsDest.Range("M" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
                 ' Write Info
                  wsDest.Range("F" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("C34").Value
                  wsDest.Range("G" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("D34").Value
                  wsDest.Range("H" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("E34").Value
                  wsDest.Range("I" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("F34").Value
                 ' Write Information
                  wsDest.Range("J" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("C33").Value
                  wsDest.Range("K" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("D33").Value
                  wsDest.Range("L" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("E33").Value
                  wsDest.Range("M" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("F33").Value

 

[Norie]

It's not clear what row you want to use.

If you do want to use the currently active row you can use something like this:

Set rng = wsDest.Range("F" & ActiveCell.Row).Resize(, 8)
 
With rng
       .NumberFormat = "0"
       .HorizontalAlignment = xlRight
End With
 
Set rng = wsDest.Range("F" & ActiveCell.Row).Resize(, 4)

With rng
     .Value = ws.Range("C33:F33").Value
     .Offset(, 4).Value = ws.Range("C34:F44").Value
End With

 

[newcoder]

Hello and thank you for the response...

Yes I want to use the current row that is being written to.

I tried your code and could not get it to work so I but a msgbox and ActiveCell.Row returns 3, 3, 3, then 6, 6, 6 then back to 3, 3, 3
I am surely doing something wrong.

I need to just select the row that is currently being written to, columns F thru M, then set the NumberFormat = "0"

something like
Range("F"->CurrentRow:"M"->CurrentRow).select
Selection.NumberFormat="0"

Dim rng as a Range
 
    Set rng = wsDest.Range("F" & ActiveCell.Row).Resize(, 8)
          With rng
                 .NumberFormat = "0"
                 .HorizontalAlignment = xlRight
          End With

Really what I want to do is compact this code. This program takes data from one workbook and places into another... As the values are written, I need to update the format and alignment. I don't think I can do this at a sheet level since this is a variance report and above and below these numbers are percentages... Basically every other row needs to be formatted this way..

I hope this makes sense.

I was only interested in streamlining the following code.. It basically formats the current line, then when it is called again it formats the next line. I really just begged/borrowed and stealed to figure this part out. But again, surely there is a better way than just copying these lines.

'                  wsDest.Range("F" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
'                  wsDest.Range("F" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
'                  wsDest.Range("G" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
'                  wsDest.Range("G" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
'                  wsDest.Range("H" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
'                  wsDest.Range("H" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
'                  wsDest.Range("I" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
'                  wsDest.Range("I" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
'                  wsDest.Range("J" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
'                  wsDest.Range("J" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
'                  wsDest.Range("K" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
'                  wsDest.Range("K" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
'                  wsDest.Range("L" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
'                  wsDest.Range("L" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight
'                  wsDest.Range("M" & Rows.Count).End(xlUp).Offset(1).NumberFormat = "0"
'                  wsDest.Range("M" & Rows.Count).End(xlUp).Offset(1).HorizontalAlignment = xlRight

Thanks in advance...

 

[Norie]

The code I posted should do what you want and it is kind of compact.

I'm still not 100% clear about which row you want to use, is it the active row or the row you are putting data into?

If all you really want to do is compact the existing code:

wsDest.Range("F" & Rows.Count).End(xlUp).Resize(,8).NumberFormat = "0"
wsDest.Range("F" & Rows.Count).End(xlUp).Resize(,8).HorizontalAlignment = xlRight

 

[newcoder]

bingo... exactly what I needed...

thanks.