| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Paste values - macro
Hi all
The following macro removes the formula in a cell. Sub cpyvalue() Dim i As Long, j As Long, K As Long, tmp As Long j = Worksheets("school").Range("k1").Value For i = 3 To j - 3 For K = 39 To 41 If Cells(i, K) = 0 Then tmp = Cells(i, K - 9) Cells(i, K - 9) = tmp If Cells(i, K + 3) = 0 Then tmp = Cells(i, K - 6) Cells(i, K - 6) = tmp End If End If Next K Next i Range("m2").Select End Sub It works fine. I want to incorporate two conditions. 1) If the cells(i, k-9) or cells(i, k-6) contains formula then only execute the for loop of k. 2) Inside the for (k) loop, once Cells(i, K) = 0 condition fails, break k-loop and goto next i. The formula in cells(i, k-9) and cells(i, k-6) is simple sumif. Any suggestions. Thanks all in advance. With regards Sreedhar |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Paste values - macro
For i = 3 To j - 3
If Cells(i, K - 9).HasFormula and Cells(i, K - 6).HasFormula Then For K = 39 To 41 If Cells(i, K) = 0 Then tmp = Cells(i, K - 9) Cells(i, K - 9) = tmp If Cells(i, K + 3) = 0 Then tmp = Cells(i, K - 6) Cells(i, K - 6) = tmp End If Else: Exit For GoTo lap End If End If Next K lap: Next i HIH On 24 Mar, 09:18, yshridhar wrote: Hi all The following macro removes the formula in a cell. Sub cpyvalue() * * Dim i As Long, j As Long, K As Long, tmp As Long * * j = Worksheets("school").Range("k1").Value * * For i = 3 To j - 3 * * * For K = 39 To 41 * * * * If Cells(i, K) = 0 Then * * * * * * tmp = Cells(i, K - 9) * * * * * * Cells(i, K - 9) = tmp * * * * * * If Cells(i, K + 3) = 0 Then * * * * * * tmp = Cells(i, K - 6) * * * * * * Cells(i, K - 6) = tmp * * * * * * End If * * * * End If * * * *Next K * * Next i * * Range("m2").Select End Sub It works fine. *I want to incorporate two conditions. 1) If the cells(i, k-9) or cells(i, k-6) contains formula then only execute the for loop of k. * 2) Inside the for (k) loop, once Cells(i, K) = 0 condition fails, break k-loop and goto next i. The formula in cells(i, k-9) and cells(i, k-6) is simple sumif. Any suggestions. *Thanks all in advance. With regards Sreedhar For i = 3 To j - 3 If Cells(i, K - 9).HasFormula and Cells(i, K - 6).HasFormula Then For K = 39 To 41 If Cells(i, K) = 0 Then tmp = Cells(i, K - 9) Cells(i, K - 9) = tmp If Cells(i, K + 3) = 0 Then tmp = Cells(i, K - 6) Cells(i, K - 6) = tmp End If Else: Exit For GoTo lap End If End If Next K lap: Next i |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Paste values - macro
Thanks alot JK. .hasformula has saved much of the time. Your modification
works for me. With regards Sreedhar "Jarek Kujawa" wrote: For i = 3 To j - 3 If Cells(i, K - 9).HasFormula and Cells(i, K - 6).HasFormula Then For K = 39 To 41 If Cells(i, K) = 0 Then tmp = Cells(i, K - 9) Cells(i, K - 9) = tmp If Cells(i, K + 3) = 0 Then tmp = Cells(i, K - 6) Cells(i, K - 6) = tmp End If Else: Exit For GoTo lap End If End If Next K lap: Next i HIH On 24 Mar, 09:18, yshridhar wrote: Hi all The following macro removes the formula in a cell. Sub cpyvalue() Dim i As Long, j As Long, K As Long, tmp As Long j = Worksheets("school").Range("k1").Value For i = 3 To j - 3 For K = 39 To 41 If Cells(i, K) = 0 Then tmp = Cells(i, K - 9) Cells(i, K - 9) = tmp If Cells(i, K + 3) = 0 Then tmp = Cells(i, K - 6) Cells(i, K - 6) = tmp End If End If Next K Next i Range("m2").Select End Sub It works fine. I want to incorporate two conditions. 1) If the cells(i, k-9) or cells(i, k-6) contains formula then only execute the for loop of k. 2) Inside the for (k) loop, once Cells(i, K) = 0 condition fails, break k-loop and goto next i. The formula in cells(i, k-9) and cells(i, k-6) is simple sumif. Any suggestions. Thanks all in advance. With regards Sreedhar For i = 3 To j - 3 If Cells(i, K - 9).HasFormula and Cells(i, K - 6).HasFormula Then For K = 39 To 41 If Cells(i, K) = 0 Then tmp = Cells(i, K - 9) Cells(i, K - 9) = tmp If Cells(i, K + 3) = 0 Then tmp = Cells(i, K - 6) Cells(i, K - 6) = tmp End If Else: Exit For GoTo lap End If End If Next K lap: Next i |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Paste values - macro
Thanks Don for your suggestion.
regards Sreedhar "Don Guillett" wrote: You may prefer a quicker way such as range("yourrange").value=range("yourrange").value -- Don Guillett Microsoft MVP Excel SalesAid Software "yshridhar" wrote in message ... Hi all The following macro removes the formula in a cell. Sub cpyvalue() Dim i As Long, j As Long, K As Long, tmp As Long j = Worksheets("school").Range("k1").Value For i = 3 To j - 3 For K = 39 To 41 If Cells(i, K) = 0 Then tmp = Cells(i, K - 9) Cells(i, K - 9) = tmp If Cells(i, K + 3) = 0 Then tmp = Cells(i, K - 6) Cells(i, K - 6) = tmp End If End If Next K Next i Range("m2").Select End Sub It works fine. I want to incorporate two conditions. 1) If the cells(i, k-9) or cells(i, k-6) contains formula then only execute the for loop of k. 2) Inside the for (k) loop, once Cells(i, K) = 0 condition fails, break k-loop and goto next i. The formula in cells(i, k-9) and cells(i, k-6) is simple sumif. Any suggestions. Thanks all in advance. With regards Sreedhar |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro for copy and paste values | Excel Discussion (Misc queries) | |||
| A macro to paste values | Excel Discussion (Misc queries) | |||
| Paste Values Macro | New Users to Excel | |||
| Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
| Macro needed to Paste Values and prevent Macro operation | Excel Discussion (Misc queries) | |||
Linear Mode
