Page 1 of 1
code help
Posted: Sat Oct 08, 2022 8:23 pm
by sna
Hi dear
I have this 2 sheets where in the expected purchases sheet row2, I got the values with the help of formulas connected to the already existing 2 sheets. I need your expertise to convert the formulas into code and get the data in Purchases sheet. The code to create the purchases sheet with headings is working.
with the help of a code, insert formulas in each column as shown in the purchases sheet row2
resize the data in Purchases with 2B
The new created purchases sheet should match the expected result as shown without the formulas.
Thank you in advance.
Re: code help
Posted: Sun Oct 09, 2022 3:46 pm
by sna
Cancel
Re: code help
Posted: Sun Oct 09, 2022 3:53 pm
by sna
Hi there,
Please skip above question,I asked for advice on VBA. Actions to do 1. Separate the data in the L line into Room# and the last name in the M and N lines. 2. Rearrange the sheet "Working" data in the Sheet"MOVECOLUMN" Result: Just press one system and you can do it To the above action question 1 How can I use vba to write the formulas of m and n lines, and then automatically run to the last line with data 2 How to change the formulas to the value in vba in the m and n lines
Re: code help
Posted: Sun Oct 09, 2022 3:55 pm
by sna
here is a template
Re: code help
Posted: Sun Oct 09, 2022 5:57 pm
by sna
Hi there,
I need your help how to split text
Code: Select all
Sub SplitText()
Range("m3").Select
Do While ActiveCell.Offset(0, -1) <> ""
ActiveCell = Mid(Split(ActiveCell.Offset(0, -1), "/")(0), 3, 99)
ActiveCell.Offset(0, 1) = Split(ActiveCell.Offset(0, -1), "/")(1)
ActiveCell.Offset(1, 0).Range("A1").Select
Loop
End Sub
I don't want to loop like this
Re: code help
Posted: Mon Oct 10, 2022 6:35 am
by snasui
The example code is below:
Code: Select all
Sub SplitTextAndRearrangeCols()
Dim rall As Range, r As Range
Dim rw As Long
With Worksheets("working")
Set rall = .Range("l3", .Range("l" & .Rows.Count).End(xlUp))
rw = rall.Rows.Count
For Each r In rall
r.Offset(0, 1).Resize(1, 2).Value = VBA.Split(VBA.Replace(r.Value, "Rm", ""), "/")
Next r
With Worksheets("movecloumn")
.Range("a2").Resize(rw).Value = rall.Parent.Range("b3").Resize(rw).Value
.Range("b2").Resize(rw).Value = rall.Parent.Range("g3").Resize(rw).Value
.Range("d2").Resize(rw).Value = rall.Parent.Range("c3").Resize(rw).Value
.Range("e2").Resize(rw).Value = rall.Parent.Range("m3").Resize(rw).Value
.Range("g2").Resize(rw).Value = rall.Parent.Range("n3").Resize(rw).Value
End With
End With
End Sub
Re: code help
Posted: Mon Oct 10, 2022 7:28 pm
by sna
Thank you, always awesome
