| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Simplifying VBA code
Hi All
The following code works fine, but for my information and education, is there a simpler way to write it ? range("F12").Copy range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats) range("H12").Copy range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats) range("I12").Copy range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats) Thanks for any assistance Michael M |
|
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simplifying VBA code
maybe where col F =6
for i = 6 to 8 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i -- Don Guillett SalesAid Software "Michael M" wrote in message ... Hi All The following code works fine, but for my information and education, is there a simpler way to write it ? range("F12").Copy range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats) range("H12").Copy range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats) range("I12").Copy range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats) Thanks for any assistance Michael M |
|
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simplifying VBA code
Thanks Don
That's a little left field, but I'll try anything to improve by VBA knowledge Regards Michael M "Don Guillett" wrote: maybe where col F =6 for i = 6 to 8 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i -- Don Guillett SalesAid Software "Michael M" wrote in message ... Hi All The following code works fine, but for my information and education, is there a simpler way to write it ? range("F12").Copy range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats) range("H12").Copy range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats) range("I12").Copy range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats) Thanks for any assistance Michael M |
|
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simplifying VBA code
Don,
The original request was for columns F,H and I and therfore a step in the for loop is required: for i = 6 to 10 step 2 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Don Guillett" wrote: maybe where col F =6 for i = 6 to 8 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i -- Don Guillett SalesAid Software "Michael M" wrote in message ... Hi All The following code works fine, but for my information and education, is there a simpler way to write it ? range("F12").Copy range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats) range("H12").Copy range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats) range("I12").Copy range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats) Thanks for any assistance Michael M |
|
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simplifying VBA code
But wouldn't this act on columns F, H and J ?
Pete Martin Fishlock wrote: Don, The original request was for columns F,H and I and therfore a step in the for loop is required: for i = 6 to 10 step 2 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Don Guillett" wrote: maybe where col F =6 for i = 6 to 8 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i -- Don Guillett SalesAid Software "Michael M" wrote in message ... Hi All The following code works fine, but for my information and education, is there a simpler way to write it ? range("F12").Copy range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats) range("H12").Copy range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats) range("I12").Copy range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats) Thanks for any assistance Michael M |
|
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simplifying VBA code
Pete
Thanks for pointing that out. It makes the code a little more difficult to simplify, I would in this case move it to a seperate subroutine and call them sepeately. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Pete_UK" wrote: But wouldn't this act on columns F, H and J ? Pete Martin Fishlock wrote: Don, The original request was for columns F,H and I and therfore a step in the for loop is required: for i = 6 to 10 step 2 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Don Guillett" wrote: maybe where col F =6 for i = 6 to 8 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i -- Don Guillett SalesAid Software "Michael M" wrote in message ... Hi All The following code works fine, but for my information and education, is there a simpler way to write it ? range("F12").Copy range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats) range("H12").Copy range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats) range("I12").Copy range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats) Thanks for any assistance Michael M |
|
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simplifying VBA code
Modifying Don's code slightly, you could do this:
for i = 6 to 9 If i = 7 Then i = 8 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i Although I don't like modifying the loop control variable. Pete On Jan 24, 11:11 am, Martin Fishlock wrote: Pete Thanks for pointing that out. It makes the code a little more difficult to simplify, I would in this case move it to a seperate subroutine and call them sepeately. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Pete_UK" wrote: But wouldn't this act on columns F, H and J ? Pete Martin Fishlock wrote: Don, The original request was for columns F,H and I and therfore a step in the for loop is required: for i = 6 to 10 step 2 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Don Guillett" wrote: maybe where col F =6 for i = 6 to 8 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i -- Don Guillett SalesAid Software "Michael M" wrote in message ... Hi All The following code works fine, but for my information and education, is there a simpler way to write it ? range("F12").Copy range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats) range("H12").Copy range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats) range("I12").Copy range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats) Thanks for any assistance Michael M- Hide quoted text -- Show quoted text - |
|
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
Simplifying VBA code
A novel (to me) approach. I kind of like it. Just tested with no bad result.
Got 6,8,9 Sub modifyloop() For i = 6 To 9 If i = 7 Then i = 8 MsgBox i 'Cells(12, i).Copy 'Range(Cells(13, i), Cells(lrow - 1, i)).PasteSpecial (xlPasteFormats) Next i End Sub -- Don Guillett SalesAid Software "Pete_UK" wrote in message oups.com... Modifying Don's code slightly, you could do this: for i = 6 to 9 If i = 7 Then i = 8 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i Although I don't like modifying the loop control variable. Pete On Jan 24, 11:11 am, Martin Fishlock wrote: Pete Thanks for pointing that out. It makes the code a little more difficult to simplify, I would in this case move it to a seperate subroutine and call them sepeately. -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Pete_UK" wrote: But wouldn't this act on columns F, H and J ? Pete Martin Fishlock wrote: Don, The original request was for columns F,H and I and therfore a step in the for loop is required: for i = 6 to 10 step 2 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i -- Hope this helps Martin Fishlock, Bangkok, Thailand Please do not forget to rate this reply. "Don Guillett" wrote: maybe where col F =6 for i = 6 to 8 cells(12,i).copy range(cells(13,i),cells(lrow-1,i)).PasteSpecial (xlPasteFormats) next i -- Don Guillett SalesAid Software "Michael M" wrote in message ... Hi All The following code works fine, but for my information and education, is there a simpler way to write it ? range("F12").Copy range("F13:F" & lrow - 1).PasteSpecial (xlPasteFormats) range("H12").Copy range("H13:H" & lrow - 1).PasteSpecial (xlPasteFormats) range("I12").Copy range("I13:I" & lrow - 1).PasteSpecial (xlPasteFormats) Thanks for any assistance Michael M- Hide quoted text -- Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| VBA: For Count, when count changes from cell to cell | Excel Discussion (Misc queries) | |||
| Cell value not recognized by code. | Excel Discussion (Misc queries) | |||
| Text formatting | Excel Worksheet Functions | |||
| VLOOKUP for Zip Code Ranges | Excel Worksheet Functions | |||
| Macro for changing text to Proper Case | Excel Worksheet Functions | |||
Linear Mode
