This code copy cell b with formula and i want to cell b copy without formula

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

This code copy cell b with formula and i want to cell b copy without formula

Post by Niranjanmeyda »

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

User avatar
HansV
Administrator
Posts: 78241
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

Post by HansV »

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:

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

Niranjanmeyda
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

Post by Niranjanmeyda »

can you add time interval in this code
but not application.wait
and it should not be look like excel is refreshing every second

User avatar
Doc.AElstein
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

Post by Doc.AElstein »

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

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

User avatar
HansV
Administrator
Posts: 78241
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

Post by HansV »

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

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

HOW TO MAKE THIS CODE FOR RANGE

Post by Niranjanmeyda »

Cells(1, r + 1).Value = Cells(r, 1).Value

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

Like this is ... Transposing A1:A4 to B1:E1

Post by Doc.AElstein »

Sub Transposing()
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

Niranjanmeyda
Lounger
Posts: 25
Joined: 18 Feb 2021, 18:08

Re: Like this is

Post by Niranjanmeyda »

Doc.AElstein wrote:
20 Feb 2021, 07:58
Sub Transposing()
For r = 1 To 4
Cells(1, r + 1).Value = Cells(r, 1).Value
Next r
End Sub
if i want to select two column a and b
and want to transpose it

User avatar
Doc.AElstein
BronzeLounger
Posts: 1499
Joined: 28 Feb 2015, 13:11
Location: Hof, Bayern, Germany

A1:A4 to C1:F1 B1:B4 to C2:F2

Post by Doc.AElstein »

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
or

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