Function to move/copy& edit - do stuff - to cells or any variable - and paste in another part of the workbook/sheet - Done

Spyros13

Board Regular
Joined
Mar 12, 2014
Messages
105
Yes,

I believe ive done it.

Anyone got any comments/queries please say.

Code:
Function MOVEME27(a As Variant, b As Variant, Optional CELLR As Variant, Optional cellq As Variant) '21/05/2018 works copied to ar4


'' 03/06/2019 23:30 was cellr as range , cellq as range - changed to variants
 Dim WTVR1 As Variant '' ''20/05/2019''
 Dim WTVR2 As Variant
 Dim P As String
 Dim P1 As String
 Dim bb As String
 Dim bb1 As String
 Dim A1 As Long
 Dim A2 As Long
 
 Dim c As String
 
 'x' a = Evaluate(a)
P = Chr(34) & a & Chr(34)
P2 = Chr(34) & [P] & Chr(34)


bb = Chr(34) & b & Chr(34)
bb1 = Chr(34) & [bb] & Chr(34)


c = Chr(34) & CELLR & Chr(34)
f = Chr(34) & callq & Chr(34)




'P2 = Chr(34) & "'''" & [P] & "'''" & Chr(34)
'p1 = Chr(34) & p & Chr(34)
    
''WTVR1 = "MOVEUS1(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & Chr(34) & P2 & Chr(34) & "," & b & ")"
   WTVR1 = "MOVEUS11h(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & [P2] & "," & [bb1] & ")"
Evaluate WTVR1


    
WTVR2 = "MOVEUS22h(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & [P2] & "," & [bb1] & ")" ' used or be adjacent - maybe redo rhat pr put a GO TO sub. '' ''20/05/2019''
    
Evaluate WTVR2


A1 = cellq.Row
A2 = cellq.Column


CELLRR = Chr(34) & CELLR & Chr(34)
CELLRR1 = Chr(34) & [CELLRR] & Chr(34)
cellqq = Chr(34) & cellq & Chr(34)
cellqq1 = Chr(34) & [cellqq] & Chr(34)




''wtvr3 = "CopyFrom.Parent.Evaluate CopyOver234h(" & c & "," & f & ")" ''''20190531 1929
wtvr31 = "MOVEUS33h(" & Application.Caller.Offset(A1 - ActiveCell.Row, A2 - ActiveCell.Column).Address(False, False) & "," & [CELLRR] & "," & [cellqq] & ")"


    Evaluate wtvr31
    
MOVEME27 = "Hello world       " & " / " & WTVR1 & " / " & WTVR2 & "\\\\\/////" & wtvr31 & "\\\\\/////---" & ActiveCell.Row - A1 & "//////---" & ActiveCell.Column - A2


' DO AS WHATVER = "MOVEUS3(" APPLICATION.CALLER.OFFSET(THE ROW & COLUMN IE CELL YOU REFERENCES IN a as variant (copy from)'
    'with ="" in sub 30052019 19:28
    
    'CopyFrom.Parent.Evaluate "CopyOver2(" & CELLR.Address(False, 1) & "," & CELLR.Address(False, False) & ")"  ''''2019050 1929
                        
                                                
                        
End Function


Private Sub MOVEUS11h(CELL1 As Range, G1 As Variant, G2 As Variant)


    '[ak333] = a




    CELL1 = Chr(34) & G1 & Chr(34) & "B" & "//" & G2




End Sub
    


Private Sub MOVEUS22h(CELL2 As Range, G3 As Variant, G4 As Variant)
   
    CELL2 = Chr(34) & G3 & Chr(34) & "<>" & G4
    
End Sub


'' with chr(34) arond the p's and a's in sub or fucntion changes behavior. thinking of doing if a is string, then a=x , x as string, if not kep as variant
''27/05/2019 :(


'''''30/05/2019 .....'''''''




'------------------------------------------------------------------------------- ADD THIS 30052019 -------------------------------
'private sub Movus3(cellfrom as range, cellto as range)
'End Sub


Private Sub moveus33h(cell3 As Range, CopyFrom As Variant, copyTo As Variant) ''''2019050 1929 ''' 03062019 change ema back to as Range here. :)
   '' copyTo.Value = CopyFrom.Value ''''2019050 1929
   
   ''CopyFrom.Value = ""
   
   cell3 = Chr(34) & CopyFrom & Chr(34) 'Chr(34) & CopyFrom & Chr(34)
   
End Sub ''''2019050 1929

&

Code:
Function MOVEME25(a As Variant, b As Variant, Optional CELLR As Range, Optional cellq As Range) '21/05/2018 works copied to ar4
 Dim WTVR1 As Variant '' ''20/05/2019''
 Dim WTVR2 As Variant
 Dim P As String
 Dim P1 As String
 Dim bb As String
 Dim bb1 As String
 
 'x' a = Evaluate(a)
P = Chr(34) & a & Chr(34)
P2 = Chr(34) & [P] & Chr(34)


bb = Chr(34) & b & Chr(34)
bb1 = Chr(34) & [bb] & Chr(34)


'P2 = Chr(34) & "'''" & [P] & "'''" & Chr(34)
'p1 = Chr(34) & p & Chr(34)
    
''WTVR1 = "MOVEUS1(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & Chr(34) & P2 & Chr(34) & "," & b & ")"
   WTVR1 = "MOVEUS110(" & Application.Caller.Offset(0, 2).Address(False, False) & "," & [P2] & "," & b & ")"
Evaluate WTVR1


    
WTVR2 = "MOVEUS220(" & Application.Caller.Offset(0, 1).Address(False, False) & "," & [P2] & "," & [bb1] & ")" ' used or be adjacent - maybe redo rhat pr put a GO TO sub. '' ''20/05/2019''
    
Evaluate WTVR2


wtvr3 = "CopyFrom.Parent.Evaluate CopyOver2346(" & CELLR.Address(False, False) & "," & cellq.Address(False, False) & ")" ''''20190531 1929
    Evaluate wtvr3
    
    wtvr4 = "CopyFrom.Parent.Evaluate CopyOver2344(" & CELLR.Address(False, False) & "," & cellq.Address(False, False) & ")" ''''20190531 1929
    Evaluate wtvr4
    
MOVEME25 = "Hello world       " & " / " & WTVR1 & " / " & WTVR2


' DO AS WHATVER = "MOVEUS3(" APPLICATION.CALLER.OFFSET(THE ROW & COLUMN IE CELL YOU REFERENCES IN a as variant (copy from)'
    'with ="" in sub 30052019 19:28
    
    'CopyFrom.Parent.Evaluate "CopyOver2(" & CELLR.Address(False, 1) & "," & CELLR.Address(False, False) & ")"  ''''2019050 1929
                        
                                                
                        
End Function


Private Sub MOVEUS110(CELL1 As Range, G1 As Variant, G2 As Variant)


    '[ak333] = a




    CELL1 = Chr(34) & G1 & Chr(34) & "B" & "//" & G2




End Sub
    


Private Sub MOVEUS220(CELL2 As Range, G3 As Variant, G4 As Variant)
   
    CELL2 = Chr(34) & G3 & Chr(34) & "<>" & G4
    
End Sub


'' with chr(34) arond the p's and a's in sub or fucntion changes behavior. thinking of doing if a is string, then a=x , x as string, if not kep as variant
''27/05/2019 :(


'''''30/05/2019 .....'''''''




'------------------------------------------------------------------------------- ADD THIS 30052019 -------------------------------
'private sub Movus3(cellfrom as range, cellto as range)
'End Sub






Private Sub CopyOver2346(CopyFrom As Range, copyTo As Range) ''''2019050 1929
   '' copyTo.Value = CopyFrom.Value ''''2019050 1929
   
   ''CopyFrom.Value = ""
   Application.Calculation = xlCalculationManual
  copyTo.Value = CopyFrom.Value
  
  Application.Calculation = xlCalculationManual
''Application.Wait (Now + TimeValue("00:00:01"))
  
  ''CopyFrom.Value = ""
   
End Sub ''''2019050 1929




Private Sub copyover2344(CopyFrom As Range, copyTo As Range)
Application.Calculation = xlCalculationManual
CopyFrom.Value = ""
Application.Calculation = xlCalculationManual
End Sub
I want to upload my spreadsheet or indeed even a snapshot of the code in action. I would really appreciate comments and/or suggestions.

very tired from running around, work & working on this. Allow me to say/forgive me to say im proud of myself.

[duplictae from here you can see whre I was. ] Ive had to learn VBA to do this.

Im trying to add a screenshot....
 
Last edited:

Spyros13

Board Regular
Joined
Mar 12, 2014
Messages
105
Ive edited my final moveme27 fn, so that the output is without double quotes. (I also know I need to +1 in the offsett to get the position right)
 

Forum statistics

Threads
1,077,688
Messages
5,335,666
Members
399,033
Latest member
thefinu

Some videos you may like

This Week's Hot Topics

Top