Looking for vba or formula help

[azrcguy]

Hello all been searching for a while now and cant find what I'm looking for. Still new and trying to teach myself to write vba codes

I'm trying to loop through the J column on my sheet (which changes size due to inserting rows) and looks for the word "install" if it finds it, grab the number that is in the C column and perform a small formula (value in C *20/60) and put that result in the K column.

any help on this would be greatly appreciated

 

[LegendaryAxe]

Does this work?

Book3
	C	D	E	F	G	H	I	J	K
2	5	DATA	DATA	DATA	DATA	DATA	DATA	INSTALL	1.666667
3	2	DATA	DATA	DATA	DATA	DATA	DATA	DROP
4	1	DATA	DATA	DATA	DATA	DATA	DATA	INSTALL	0.333333
Sheet1

Cell Formulas
Range	Formula
K2:K4	K2	=IF(ISNUMBER(SEARCH("install",J2)),C2*20/60,"")

 

[azrcguy]

its returning 0 on my sheet if I drag that down. I assume i has something to do with the fact column C is a merged cell so that number doesn't always line up with the word "install"
I had the formula below at one time but it breaks often because the sheet constantly changes the amount of rows. Because it changes, id like to have a form control and a macro assigned that loops through that entire column then displays the result

 

[LegendaryAxe]

Is the first number in Column C in C7, C8, or C9?

 

[SeniorNewbie]

Hi, you were asking for a VBA solution: here you go. Copy this code to the sheet:

Option Explicit

Sub IfInstall()
Dim i  As Long, r As Long                       'two variables to loop through the existing data
r = Cells(Rows.Count, 3).End(xlUp).Row          'find the last row with data in column 3

    For i = 1 To r                              'loop through the data from row 1 - edit this to your needs
        If Cells(i, 10) = "install" Then        'check value in all rows and column 10 (=J)
            Cells(i, 11) = Cells(i, 3) / 3      'if found calculate a simplified division
        End If                                  'end check
    Next                                        'loop

End Sub

To start the sub open the macro dialog with alt+f8 and no need to copy formulas to any rows

Have fun

 

[azrcguy]

the actual first row of the sheet would be C14 with the next group of rows being C17.
C column has been assigned a name value in the top most row of each group designated PC_1....PC_2...and so on

each "group" of rows starts as 3 rows but can quickly change depending on how many order numbers there are. C column is piece counts. So when we see "install" we are looking at how many pieces there are and calculating the amount of time

 

[SeniorNewbie]

Okay - a modified version to replace the old code

Option Explicit

Sub IfInstall()
Dim i  As Long, r As Long                       'two variables to loop through the existing data
Dim s As Integer                                'a new var for the first row
r = Cells(Rows.Count, 3).End(xlUp).Row          'find the last row with data in column 3

s = FirstRow(3)                                 'calls a function to find the first row in column 3 (=C)

    For i = s To r                              'loop through the data
        If Cells(i, 10) = "install" Then        'check value in all rows and column 10 (=J)
            Cells(i, 11) = Cells(i, 3) / 3      'if found calculate a simplified division
        End If                                  'end check
    Next                                        'loop
'MsgBox "!"
End Sub

Function FirstRow(iCol As Integer)
Dim i As Integer

For i = 1 To 50                                 'assuming the first row smaller than 50
    If IsNumeric(Cells(i, iCol)) Then           'checking if the cell is numeric
        FirstRow = i                            's found
    End If
Next i
End Function

Have fun !

 

[azrcguy]

Thank you guys. ill have to look into it on monday and report back

 

[Peter_SSs]

Welcome to the MrExcel board!

ill have to look into it on monday

Another thing to look into to get the most from the forum is XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.
(If you have trouble with XL2BB, review the "XL2BB Icons greyed out" link in the 'Known XL2BB issues' section near the top of the XL2BB Instructions page linked above.)

 

[Peter_SSs]

the actual first row of the sheet would be C14

Given that and assuming the values in column J are not the result of formulas, try this with a copy of your workbook.
This does not test every row in the range, just those that have something in column J. So in my sample sheet below, only 3 tests needed.

Sub Test()
  Dim c As Range
  
  For Each c In Range("J14", Range("J" & Rows.Count).End(xlUp)).SpecialCells(xlConstants)
    If UCase(c.Value) = "INSTALL" Then c.Offset(, 1).MergeArea.Cells(1).Value = c.Offset(, -7).MergeArea.Cells(1).Value / 3
  Next c
End Sub

Here is my test sheet after running the code on a sheet with different sized merged areas.

azrcguy.xlsm
	B	C							J	K
14		5								1.666667
15	DATA								INSTALL
16
17		2
18
19
20	DATA								DROP
21
22		1								0.333333
23	DATA								INSTALL
24
25
Install