Adding text into an inserted cell based on adjacent cell's text.

[bigbeat85]

I'm very new to VBA so trying my best to get my head round it.

What I want to create is a tool that when I opened

insert a new column and name it. ( I have worked out this! hah!)

then based on the adjacent cell populate the newly created column with particular word based on the adjacent cells text.

Eg.

If any cells in column B contain 'Are there uneeded materials (pallets, raw materials, boxes etc) or tools in the Work Area?'

populate Cell A2 with 'SORT'


Then the nest would be,

If the cell in column A equals 'a particular word' , apply 'this formula' in another cell in the same row.


Thanks

N

 

[Candyman8019]

You could try something like this:

Private Sub BigBeat()
Dim rng As Range
Dim Lrow As Long

Lrow = Sheets("sheet10").Cells.Find("*", searchorder:=xlByRows, searchdirection:=xlPrevious).Row

'Determine if sheet needs to be sorted
Set rng = Sheets("sheet10").Range("B2:B" & Lrow)

    For Each cell In rng
        If cell.Value = "Are there uneeded materials (pallets, raw materials, boxes etc) or tools in the Work Area?" Then
           Sheets("sheet10").Range("A1").Value = "Sort"
        End If
    Next
    
'Apply Formula as required
Set rng = Sheets("sheet10").Range("A2:A" & Lrow)

    For Each cell In rng
        If cell.Value = "a particular word" Then
           cell.Offset(0, 3).Formula = "=1+1"
        End If
    Next
End Sub

 

[bigbeat85]

Thank you for that.

I have had a play and came up with the solution of this;

Set rng = Range(Range("B2"), Range("B" & Rows.Count).End(xlUp))

For Each cell In rng

Select Case cell.Value

Case "Are there uneeded materials (pallets, raw materials, boxes etc) or tools in the Work Area?"

cell.Offset(0, -1).Value = "SORT"


It seems to work on lots of data so far.

I am struggling with the formula though.

The Lrow with the "*". What is this bit supposed to be?

 

[Candyman8019]

the lrow line will find the last row of the data...similar to what you have in your rng. Just another way to do the same thing...

 

[Candyman8019]

For the formula piece, you can use similar code to what you have. for example, if you fine "your particular word" in column A, this piece would add a formula 3 columns to the right.

cell.Offset(0, 3).Formula = "=1+1"

 

[bigbeat85]

The formula I have pulls another number from a different cell. As this will be going down a list of cells, how do I add into the formula that the cell number will be different?

Formula is "=0.5-((E10/14)/2)" The only thing that changed is the number of the row.

 

[Candyman8019]

Then it would be something like:

cell.Offset(0, 3).Formula = "=0.5-((E” & range(“F5”).value & “10/14)/2)"

Of course I’m just making up a cell because I have no idea what your worksheet looks like but this should get you down the right path.

 

[bigbeat85]

That would help wouldn't it?

In column "% in control in 1 week" the formula =0.5-((E2/14)/2) is contained.
The E2 references the "flagged responses" column and would be the only cell reference that changes depending on row it is in.

Also depending on the question the formula will be used. =E5/28 Again the "E*" would change depending on what "specific word" was in the question column

Hope that is abit clearer?

stage

Question

Status

average of flagged responses

flagged responses

% in of control in 1 week

SORT	Are there unnecessary or outdated instructions, procedures, notes drawings or visual aids in the Work Area? (Visual Cleaning Standards, SOPs, drawings, etc)	Archived	45%​	5​	18%​
SHINE	Has the cleaning plan been completed?	Archived	45%​	5​	18%​

 

[bigbeat85]

Should add a bit more explanation.

Formula =0.5-((E2/14)/2) The "E2 denotes the value i want to change and the rest of the formula will remain the same. This will be applied thru the same column F as the results.

 

[bigbeat85]

Figured it out!

Range("F2").Formula = "=$E2/14"
Range("F2", "F" & Cells(Rows.Count, 1).End(xlUp).Row).FillDown