Jul 08 2022 07:55 AM - edited Jul 08 2022 08:28 AM
The subject is probably not a good summary of what I'm looking to do. But, It's the best could come up with.
I would like to know if there is a way to take range A1:A21 and make it appear as range B1:B21
I have column A and need to convert it to look like column B so that I can use those for calculation in other formulas. I was thinking it had to be something with a BYROW / LAMBDA function but I can't make it work.
Jul 08 2022 08:13 AM - edited Jul 08 2022 08:15 AM
Jul 08 2022 08:27 AM
Jul 08 2022 08:35 AM - edited Jul 08 2022 08:37 AM
The 4 numbers below it is by the range definition of A1:A21. For each cell in that range, if the cell is blank use last number found, if cell has a number use that number.
Jul 08 2022 08:57 AM
Just try with =IF(A2="",B1,A2) and First Row should be header or blank.
1 | 1 |
1 | |
1 | |
1 | |
1 | |
2 | 2 |
2 | |
2 | |
2 | |
2 | |
3 | 3 |
3 | |
3 | |
3 | |
4 | 4 |
Jul 08 2022 09:00 AM
@Jagodragon Did something with PowerQuery as I'm not comfortable with VBA. Perhaps it works for you.
Jul 08 2022 09:02 AM
Jul 08 2022 09:54 AM
Sub fill()
Dim MaxRow As Long
Dim i As Long
MaxRow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To MaxRow
Cells(i, 2).Value = Cells(i, 1).Value
If Cells(i, 1) <> "" Then
Else
Cells(i, 2).Value = Cells(i - 1, 2).Value
End If
Next i
End Sub
Assuming that there is always a value in cell A1 you can try these lines of code. In the attached file you can click the button in cell E2 to start the macro.
Jul 11 2022 06:19 AM
SolutionThank you @OliverScheurich
I didn't actually use your response because I needed to be able to set it for a specific range. But, you confirmed my fear that i would have to create a VBA function for this.
Here is the code I ultimately used.
Function Expand(x As Range) As Variant
'formula to expand values of a column range to fill all cell
' =Expand(A1:A20) or =Expand(A1#)
Application.Volatile
Dim RET As Variant 'retun table
Dim XVal 'vriable to hold current num
Dim i 'counter
i = 1 'presetting the counter
XVal = 0 'presetting the initial set value
ReDim RET(1 To x.Rows.Count, 0) 'defineing the Return array
For i = 1 To x.Rows.Count 'For each cell in the intial range dor the following
If IsNumeric(x(i).Value) = True And x(i) > 0 Then 'check if the value is a number greater than 0
XVal = x(i).Value 'if it is a nuber greater than zero,set the pass thru value to cell value
End If '
RET(i, 0) = XVal 'set cell value to updated pass thru value
Next i '
Expand = RET 'sends the processed information back to the excel sheet thru formula
End Function
Jul 11 2022 06:19 AM
SolutionThank you @OliverScheurich
I didn't actually use your response because I needed to be able to set it for a specific range. But, you confirmed my fear that i would have to create a VBA function for this.
Here is the code I ultimately used.
Function Expand(x As Range) As Variant
'formula to expand values of a column range to fill all cell
' =Expand(A1:A20) or =Expand(A1#)
Application.Volatile
Dim RET As Variant 'retun table
Dim XVal 'vriable to hold current num
Dim i 'counter
i = 1 'presetting the counter
XVal = 0 'presetting the initial set value
ReDim RET(1 To x.Rows.Count, 0) 'defineing the Return array
For i = 1 To x.Rows.Count 'For each cell in the intial range dor the following
If IsNumeric(x(i).Value) = True And x(i) > 0 Then 'check if the value is a number greater than 0
XVal = x(i).Value 'if it is a nuber greater than zero,set the pass thru value to cell value
End If '
RET(i, 0) = XVal 'set cell value to updated pass thru value
Next i '
Expand = RET 'sends the processed information back to the excel sheet thru formula
End Function