Based on two cell inputs, I need rest of the cells to give values automatically 1

[leatherland]

Hi,

Based on two cell inputs, I need rest of the cells to automatically calculate the differences and input the values.


Scenerio 1:
Eg: If I input '38 in (E1)' and '50 in (K1)', other empty cells from A1 to L1 will calculate and have their respective values automatically.

So, it will look like as below:
A1 B1 C1 D1 E1 F1 G1 H1 I1 J1 K1 L1
30 32 34 36 38 40 42 44 46 48 50 52

Scenerio 2:

Eg: If I input '16.75 in (B1)' and '18.00 in (G1)', other empty cells from A1 to H1 will calculate and have their respective values automatically.

So, it will look like as below:
A1 B1 C1 D1 E1 F1 G1 H1
16.50 16.75 17.00 17.25 17.50 17.75 18.00 18.25


***

 

[SkipVought]

leatherland, welcome to Tek-Tips.

I hope you'll take time to browse these forums and observe what's going on here and how we conduct business. Tek-Tips is a great resource for gaining knowledge and skill for your business pursuits.

1) Could you please explain the business case for your question.

2) You have stated two specific scenarios. Thank you for these specific examples. Could you please state a generalized requirement that would encompass these two and any other.

Please answer BOTH of these questions if you will.

 

[leatherland]

I am creating a size chart for clothing (jackets, suits etc).
For example: I need to supply sizing information of the Jacket. Jacket comes in 10-12 sizes (32,34,36...50,52) so It is always difficult and time consuming to take measurement for each and every jacket.
On top of that, I also need to take measurement for Jacket's Chest, Shoulder, Length, Collar etc..

I will take all the measurement of any 'two' size jacket, say size: 36(chest)and 42(chest). Once I input 36 in say (D1) and 42 in (G1), rest of the sizes will equally distribute as 30(A1), 32(B1), 34(C1), 36(D1), 38(E1), 40(F1), 42(G1), 44(H1).

The above will be repeated for Shoulder, Length, collar etc...

The following is Not so important at the moment, but ideally want to consider if it is not too tricky:
**Ideally I only want to take measurement of only 1 jacket, so rest of cells value can come up automatically.

***Once the chart is ready, It will then be converted to Image file (1000x500) so I can upload on to my website.

Kindly let me know if you still need more broader picture of the above

 

[SkipVought]

Well this is what happens when you give 2 very myopic examples: BOTH in row 1, BOTH with different end cells!

So I went off with the ASSUMPTION that you ONLY wanted ONE row evaluated to an end cell one column greater than that of the last cell value column.

So you REALLY want any row's values differences defined by 2 values for A:K, yes?

 

[SkipVought]

if only one value provided, so the rest of the row in the block automatically appears"

How could that happen? What would each INTERVAL be, based on only ONE value? Would you like the program to randomly select an interval?

 

[leatherland]

May be I have created confusion.

Just had a discussion with my staff and we have decided that, we are going to take a measurement for the 1st size and last size of the jacket.
Meaning, First and last values i.e start and end value will be always provided.


Example :
jacket 1: smallest size available in this jacket is '30' and biggest is '52' so I will be providing cell input as 30 and 52, hence automatic cell value in between will be 30,32,34,36,38,40,42,44,46,48,50,52

jacket 2: smallest size available in this jacket is say 34 and biggest size is 48, hence cell input will be 34 and 48. I will need auto cell value as 34,36,38,40,42,44,46,48

With chest measurement, I will also take measurements of Same jackets 'shoulder, collar, length'

Please refer the image file attached


Sorry I think I created confusion before, this is what I need now , thank you for your time

 

[SkipVought]

So this is another curve. Now we have RANGES on the sheet each having its own
1) different number of columns and
2) different number of rows, ie Chest, Arm, Shoulder, Length or Chest, Shoulder, Length or something else.

Yes?

So on the sheet that you want to "fill in the blanks" you may have ONE or an indeterminate number of RANGES that will be separated by at least ONE EMPTY ROW, each range having Column B and Column LAST with values in each row of the RANGE?

 

[SkipVought]

ALSO, I do not understand Row 2 and Row 10 in your .png image (images are virtually useless as they cannot be copied into a sheet and used!)

Please explain row 2 and row 10.

 

[leatherland]

Yes, that is correct.

 

[leatherland]

Row 2 and Row 10 has nothing to do with the requirements. I just done that to show that may make more sense, but You can ignore that

 

[SkipVought]

I need to see a typical example of what will be on the sheet when you have multiple ranges of different number of rows/columns.

 

[SkipVought]

...in an Excel Workbook, please.

 

[leatherland]

please find the excel file attached

 

[SkipVought]

So only One range per sheet?

So you only expect the values filled in one sheet at a time or for all sheets in the workbook?

If the latter, will there be other sheets in the workbook and if so, how will they LOGICALLY be differentiated from the sheets that need values filled in?

 

[Deniall]

I'd use a User Defined Array Function, called once per row.[ ] Something along the lines of the following, but perhaps with more checking incorporated.

Function FillRow(cStart As Double, cEnd As Double)
Dim CallerRows As Long, CallerCols As Long, I As Long
Dim Ans()
'
With Application.Caller
    CallerRows = .Rows.Count
    CallerCols = .Columns.Count
End With
ReDim Ans(CallerCols)
'
If CallerRows <> 1 Then
    MsgBox "This function can work only on a single row."
    For I = 1 To CallerCols: Ans(I) = CVErr(xlErrRef): Next I
    FillRow = Ans
    Exit Function
End If
'
For I = 1 To CallerCols
    Ans(I) = cStart + (cEnd - cStart) * I / (CallerCols + 1)
Next I
'
FillRow = Ans
End Function

If, for your typical row, you have your start value in cell[ ]C12 and your end value in cell[ ]C24, you select the range C13:C23, type
=FILLROW(C12,C24)
and hit Control-Shift-Enter.

 

[leatherland]

@Skipvought,

"So only One range per sheet?"
---One range per row per sheet


"So you only expect the values filled in one sheet at a time or for all sheets in the workbook? "
--- Yes only one sheet at a time

 

[SkipVought]

Here's my take on your problem, doing one sheet at a time: the Active Sheet.

Paste this procedure into a MODULE in your workbook's VBAProject.

Sub FillRange()
'given 2 values in one row, the FIRST and the LAST column values
'  fill the range from the FIRST to the LAST Column with values
[b]'the data range must be isolated from any other data by at least
' one empty row and
' one empty column
'the data range must start in A1, where 
' Row 1 is headers and
' Column 1 is headers[/b]

    Dim lRow As Long, i As Long, n
    Dim nDif1               'the difference in the VALUES
    Dim nDif2               'the difference in the COLUMN
    Dim nDif3               'the incrimental VALUE difference
    Dim lRow1 As Long       'first row of the UsedRange
    Dim lRow2 As Long       'last row of the UsedRange
    Dim iCol1 As Integer    'first column with a values in a row
    Dim iCol2 As Integer    'last column with a values in a row
    
    With ActiveSheet.Cells(1, 1).CurrentRegion
        lRow1 = .Row
        lRow2 = .Rows.count + lRow1 - 1
    
        For lRow = lRow1 + 1 To lRow2
            'get the two values in row
            'column A is ALWAYS text
            iCol1 = Cells(lRow, "A").End(xlToRight).Column
            iCol2 = Cells(lRow, iCol1).End(xlToRight).Column
            
            nDif1 = Cells(lRow, iCol2).Value - Cells(lRow, iCol1).Value
            nDif2 = iCol2 - iCol1
            nDif3 = nDif1 / nDif2
            
            n = Cells(lRow, iCol1).Value + nDif3
            For i = iCol1 + 1 To iCol2 - 1
                Cells(lRow, i).Value = n
                n = n + nDif3
            Next
        Next
    End With
End Sub

 

[SkipVought]

BTW, the procedure:

1) loops thru each row in the data range, regardless how many rows there are in the data range.

2) figures out where the FIRST and LAST data values are in each row

3) calculates the per-column incremental difference in the values in each row and fills in the missing values

 

[leatherland]

Hi, SkipVought,

That's absolutely brilliant. You have provided exactly what I wanted.

If you can help me update the following would be great:

1) Sometime the start and end value are closer and in that case cells on the left and right are empty.
Can it continue to fill the rest of the row cells in that particular series?


2) Once the macro is run, the empty cells are filled up which is great. But when I run macro again (rows are filled at that moment), it loops and fills the cells more till the end of the row.
I just want that, the cells has to be empty, only then the macro can be run.

 

[SkipVought]

closer" is relative. What specifically are you referring to?

I can fix 2). If there are only 2 cells with values in a row and the two cells are not adjacent, then run the process else exit for the worksheet.