This code copy cell b with formula and i want to copy without formula
Please do no remove loop
Sub Copy_with_Loop()
Dim x As Long
Dim myRange As Range
Set myRange = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))
For x = 1 To 45 ''' Change the number of loops as per your needs
myRange.Copy myRange.Offset(0, x)
Next x
End Sub
This code copy cell b with formula and i want to cell b copy without formula
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: This code copy cell b with formula and i want to cell b copy without formula
Welcome to Eileen's Lounge!
I have moved this question to a thread of its own, since it was not related to the thread in which it was posted.
Here is a modified version:
I have moved this question to a thread of its own, since it was not related to the thread in which it was posted.
Here is a modified version:
Code: Select all
Sub Copy_with_Loop()
Dim x As Long
Dim myRange As Range
Application.ScreenUpdating = False
Set myRange = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))
For x = 1 To 45 ''' Change the number of loops as per your needs
myRange.Offset(0, x).Value = myRange.Value
Next x
Application.ScreenUpdating = True
End Sub
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
Re: This code copy cell b with formula and i want to cell b copy without formula
can you add time interval in this code
but not application.wait
and it should not be look like excel is refreshing every second
but not application.wait
and it should not be look like excel is refreshing every second
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Re: This code copy cell b with formula and i want to cell b copy without formula
I will take a guess that maybe you is , or your assignment giver ( teacher is it? ) , means do simple change is to copy, or to do it slowly, like maybe one of these
https://docs.microsoft.com/en-us/office ... stespecial
https://docs.microsoft.com/en-us/office ... lpastetype
https://docs.microsoft.com/hi-in/office ... stespecial
https://docs.microsoft.com/hi-in/office ... lpastetype
Alan
https://docs.microsoft.com/en-us/office ... stespecial
https://docs.microsoft.com/en-us/office ... lpastetype
https://docs.microsoft.com/hi-in/office ... stespecial
https://docs.microsoft.com/hi-in/office ... lpastetype
Code: Select all
Sub Copy_with_Loop_Slowly()
Dim x As Long
Dim myRange As Range
Set myRange = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))
For x = 1 To 45 ''' Change the number of loops as per your needs
myRange.Copy
myRange.Offset(0, x).PasteSpecial Paste:=xlPasteValues ' https://docs.microsoft.com/de-de/office/vba/api/excel.range.pastespecial https://docs.microsoft.com/en-us/office/vba/api/excel.xlpastetype
Next x
End Sub
Sub Copy_with_Loop_VerySlowly()
Dim x As Long
Dim myRange As Range
Set myRange = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))
For x = 1 To 45 ''' Change the number of loops as per your needs
Dim ArCel As Range
For Each ArCel In myRange
'Let ArCel.Offset(0, x).Value = ArCel.Value
' Or even slower
ArCel.Copy
ArCel.Offset(0, x).PasteSpecial Paste:=xlPasteValues
Next ArCel
Next x
End Sub
Alan
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Administrator
- Posts: 78524
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: This code copy cell b with formula and i want to cell b copy without formula
And another way:
Code: Select all
Dim myRange As Range
Dim x As Long
Sub Copy_with_Loop2()
Set myRange = Range(Cells(1, "B"), Cells(Rows.Count, "B").End(xlUp))
x = 1
CopyOneColumn
End Sub
Sub CopyOneColumn()
myRange.Offset(0, x).Value = myRange.Value
If x < 45 Then
x = x + 1
' Wait 5 seconds - change as desired
Application.OnTime Now + TimeSerial(0, 0, 5), "CopyOneColumn"
End If
End Sub
Best wishes,
Hans
Hans
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
HOW TO MAKE THIS CODE FOR RANGE
Cells(1, r + 1).Value = Cells(r, 1).Value
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
Like this is ... Transposing A1:A4 to B1:E1
Sub Transposing()
For r = 1 To 4
Cells(1, r + 1).Value = Cells(r, 1).Value
Next r
End Sub
For r = 1 To 4
Cells(1, r + 1).Value = Cells(r, 1).Value
Next r
End Sub
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also
-
- Lounger
- Posts: 25
- Joined: 18 Feb 2021, 18:08
Re: Like this is
if i want to select two column a and bDoc.AElstein wrote: ↑20 Feb 2021, 07:58Sub Transposing()
For r = 1 To 4
Cells(1, r + 1).Value = Cells(r, 1).Value
Next r
End Sub
and want to transpose it
-
- BronzeLounger
- Posts: 1499
- Joined: 28 Feb 2015, 13:11
- Location: Hof, Bayern, Germany
A1:A4 to C1:F1 B1:B4 to C2:F2
Code: Select all
Sub Transposingab()
For r = 1 To 4 Step 1
Cells(1, r + 2).Value = Cells(r, 1).Value ' A1:A4 to C1:F1
Cells(2, r + 2).Value = Cells(r, 2).Value ' B1:B4 to C2:F2
Next r
End Sub
Code: Select all
Sub EvaluateRangeIt()
Range("C1:F2").Value = Application.Index(Range("A1:B4"), Evaluate("=column(A:D)"), Evaluate("=row(1:2)"))
End Sub
I am having difficulty logging in with this account just now.
You can find me at DocAElstein also
You can find me at DocAElstein also