Avoid ,,Select" and ,,Selection" in a few lines of code
 

Hi everybody , and have a pleasant Sunday

I have this code :

Sub macro55()

Application.ScreenUpdating = True

Dim FromWks1 As Worksheet
Dim DestWks As Worksheet
Dim NextRow As Long
Dim myCell As Range
Dim myRng1 As Range
Set FromWks1 = Workbooks("polo2x.xls").Worksheets("1")
Set DestWks = Workbooks("polo2x.xls").Worksheets("1")
With FromWks1
Set myRng1 = .Range("BA11:BA100")
End With
With DestWks
For Each myCell In myRng1.Cells
If myCell < 3 Then
myCell = myCell.Value + 1

Else: myCell.Offset(0, -4).Select
Selection.Cut
Selection.End(xlToLeft).Offset(0, 1).Select
ActiveSheet.Paste
End If
Next myCell
Application.CutCopyMode = False
Application.ScreenUpdating = True
End With
End Sub

Can it be removed the Select and Selection ?, (for speeding up
this code ) .

Thank you very much !

Avoid ,,Select" and ,,Selection" in a few lines of code
 

Pe duminică, 6 decembrie 2020, la 05:44:56 UTC+2, Xxer Xxes a scris:
I repost some lines of code :

With DestWks
For Each myCell In myRng1.Cells
If myCell < 3 Then
myCell = myCell.Value + 1

is wrong . I need if in myCell value is < 3 ,
then , myCell.Offset(0, -4 ) to increment its own value
with value ,,1" ;
so , if in myCell.Offset(0,-4) the value is 235 , it must became
236 ;

now, i have this line of code, but i doesnt do the task I need :
For Each myCell In myRng1.Cells
If myCell < 3 Then
myCell.Offset(0, -4).Value = Value + 1


i cant figure it out .

Avoid ,,Select" and ,,Selection" in a few lines of code
 

Any sugestion here, please ? ..

Avoid ,,Select" and ,,Selection" in a few lines of code
 

Hi,

Am Mon, 7 Dec 2020 10:29:25 -0800 (PST) schrieb Xxer Xxes:

Any sugestion here, please ? ..

try:

With DestWks
For Each myCell In myRng1.Cells
If myCell < 3 Then
myCell = myCell.Value + 1
Else
myCell.Offset(0, -4).Cut _
Destination:=.Cells(myCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1)
End If
Next myCell
Application.CutCopyMode = False
Application.ScreenUpdating = True
End With


Regards
Claus B.
--
Windows10
Office 2016

Avoid ,,Select" and ,,Selection" in a few lines of code
 

Thanks a lot for response .

With DestWks
For Each myCell In myRng1.Cells
If myCell < 3 Then
myCell = myCell.Value + 1
is wrong . I need if in myCell value is < 3 ,
then , myCell.Offset(0, -4 ) to increment its own value
with value ,,1" ;
so , if in myCell.Offset(0,-4) the value is 235 , it must became
236 ;

Well this above part I have fixed it :

If myCell < 3 Then
myCell.Offset(0, -4) = myCell.Offset(0, -4).Value + 1

This line of code make me what I need . The above part was solved , working
with your code.
- - - - -
but here , lines of code below, I still have a trouble , and in this macro is my last task
I have to accomplish :

myCell.Offset(0, -4).Cut _
Destination:=.Cells(myCell.Row, Columns.Count).End(xlToLeft).Offset(0, 1)

,,Columns.Count " starts to count from last column .
I have 5 more columns after
Column(BA) , ( it means Column(BA) ,, to the right " ) ; I use olso and the ,,myCell.Offset(0, -4)" Column with data , that means Column(AW) ,
and the ,,Cut" value must go to range IN THE LEFT of Column (AW) , when it found data
. This last part , ,,Offset(0, 1)" work very well , but the ,,Count" must begin not from last column from sheet but as i just have described .

Avoid ,,Select" and ,,Selection" in a few lines of code
 

but here , lines of code below, I still have a trouble , and in this macro is my last task

,,Columns.Count " starts to count from last column .
I have 5 more columns after
Column(BA) , ( it means Column(BA) ,, to the right " ) ; I use olso and the ,,myCell.Offset(0, -4)" Column with data , that means Column(AW) ,
and the ,,Cut" value must go to range IN THE LEFT of Column (AW) , when it found data
. This last part , ,,Offset(0, 1)" work very well , but the ,,Count" must begin not from last column from sheet but as i just have described .

Well, I just have a work arround with your code , and this lines do my task correctly :

Else: myCell.Offset(0, -4).Cut _
Destination:=.Cells(myCell.Row, myCell.Offset(0,4)).End(xlToLeft).End(xlToRight).O ffset(0, 1)

Maybe exist a little more elegant solution , or code line writeing ,
but it is already ok .

I thank you so much for your really great help ,
and helpfull assistence . I wish you a great week .