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

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

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

Paste values - macro
 

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

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