Hello.
I’m looking for a macro that allows you to paste text into the topmost available cell of a spreadsheet column.
The objective is to create an “invoice” sheet which is filled in as you enter.
I don’t know if it’s actually possible…
Thank you so much.
Hello @Arnaud1
Please provide a full description of the desired result.
I don’t really understand what do you mean by that. Take a look at simple macro that writes data into a cell. With it you can paste text in any cell you want.
Perhaps you have seen an approximately close type of desired macro that you can share with us?
Hello,
Thank you for your reply.
Attached is the VBA code I’m trying to convert:
Sub Transfert()
Dim Cell As Range
Dim Ligne, Reponse As Integer
Dim Client As String
Dim Façonnage As String
Ligne = Sheets("Transfert").Cells(100, 2).End(xlUp).Row + 1
Ligneb = Sheets("Transfert").Cells(100, 2).End(xlUp).Row + 2
If Range("D3").Value = "" Or Range("E3").Value = "" Or Range("F3").Value = "" Then
MsgBox "Missing measurement: Transfer interrupted."
Exit Sub
Else
If Ligne = 100 Or Ligneb = 100
MsgBox "Unable to transfer: table complete."
Exit Sub
Else
If Range("G3").Value = "Professionnel" Or Range("G3").Value = "Public" Then
If Range("L3").Value = "Tarif appliqué" Then
If Range("M3").Value = "" Then
MsgBox "The client is not referenced, transfer interrupted. Please select Other under the Customers box and enter the customer's name under the box: Other Customer. Thanks."
Exit Sub
End If
End If
End If
If Range("L3").Value = " Autre" Then
If Range("M3").Value = "" Then
MsgBox "Le client n'est pas référencé, transfert interrompu."
Exit Sub 'permet d'arrêter le transfert
End If
End If
Select Case Range("L3").Value
Case "Colmar"
Case "Strasbourg"
Case Else
If Range("G3").Value = "Pro Staircase" Or Range("G3").Value = "Creative Designers"
If Client = Range("L3").Value Or Client = Range("M3").Value Then
If Range("G3").Value <> Range("L3").Value Then
Reponse = MsgBox("Are you sure to apply the price " & Range("G3").Value & " to customers " & Client & " ?", vbYesNo)
If Reponse = vbNo Then
MsgBox "Bad pricing: Transfer interrupted."
Exit Sub
End If
End If
End If
End If
End Select
Application.ScreenUpdating = False
With Sheets("Transfert")
.Cells(Row, 2) = Range("O54").Value 'Designation
.Cells(Row, 3) = Range("P54").Value 'Thickness
.Cells(Row, 4) = Range("Q54").Value 'Number
.Cells(Row, 5) = Range("R54").Value 'Width
.Cells(Row, 6) = Range("S54").Value 'Height
.Cells(Row, 7) = Range("T54").Value 'Shaping
.Cells(Row, 8) = Range("U54").Value 'Shape
.Cells(Row, 9) = Range("V54").Value 'Unit PV
.Cells(Row, 10) = Range("W54").Value 'PV Total
.Cells(Row, 11) = Range("X54").Value 'PA Total
.Cells(Row, 12) = Range("Z54").Value 'Area
.Cells(Row, 13) = Range("AA54").Value 'Weight
.Cells(Lineb, 2) = Range("O55").Value 'Designation
.Cells(Lineb, 3) = Range("P55").Value 'Thickness
.Cells(Rowb, 4) = Range("Q55").Value 'Number
.Cells(Lineb, 5) = Range("R55").Value 'Width
.Cells(Lineb, 6) = Range("S55").Value 'Height
.Cells(Lineb, 7) = Range("T55").Value 'Shaping
.Cells(Lineb, 8) = Range("U55").Value 'Shape
.Cells(Lineb, 9) = Range("V55").Value 'Unit PV
.Cells(Lineb, 10) = Range("W55").Value 'PV Total
.Cells(Lineb, 11) = Range("X55").Value 'PA Total
.Cells(Lineb, 12) = Range("Z55").Value 'Area
.Cells(Lineb, 13) = Range("AA55").Value 'Weight
End With
Application.ScreenUpdating = True
End If
MsgBox "Successful transfer!"
End If
End Sub
Thanks very much
Hello again @Arnaud1
Thanks for providing example of the macro.
I can see that this macro is heavily relying on Message Boxes which, unfortunately, are not supported in our editors. Please see this thread for more information about Message box with macro.
This is very unfortunate but we won’t be able to help you in macro adaptation.
Sorry for the inconvenience.
Thank you for your reply.
And isn’t there a solution for: End(xlUp).Row + 1
Yes, we do have the solution. Please see next method in our official API:
https://api.onlyoffice.com/docbuilder/spreadsheetapi/apirange/end
Hi,
Thank you for your reply.
Can you help me please?
Can you write me this macro in javascript for onlyoffice:
Copy the contents of cell A3 of sheet “Sheet 1” to the top of row B of sheet “Sheet 2”
THANKS
I tried, but it doesn’t work…
(function()
{
var oSheet1 = Api.GetSheet(“Sheet 1”);
var oSheet2 = Api.GetSheet(“Sheet 2”);var oCell = oSheet1.GetRange(“A3”);
var sValue = oCell.GetValue();
var oRange = oSheet2.GetRange(“B1:B20”);oRange.End(“xlUp”).SetValue(sValue);
})();
var oWorksheet1 = Api.GetSheet(“Feuille1”);
var oWorksheet2 = Api.GetSheet(“Feuille2”);
var oRange = oWorksheet1.GetRange(“A1”);// Getting the active sheet var activeSheet = Api.GetSheet("Feuille1"); // Minimum row index var indexRowMin = 0; // Maximum row index var indexRowMax = 1048576; // Column 'A' var indexCol = 0; // Row index for empty cell search var indexRow = indexRowMax; for (; indexRow >= indexRowMin; --indexRow) { // Getting the cell var range = activeSheet.GetRangeByNumber(indexRow, indexCol); // Checking the value if (range.GetValue() && indexRow !== indexRowMax) { range = activeSheet.GetRangeByNumber(indexRow + 1, indexCol); ran ge.Select(); break; } }oRange.Copy(oWorksheet2.GetRange(“A:A”).End(“xlUp”));
})();