Posts by Km0730

Km0730 · Feb 12th 2018

Thank you for the quick response.

When I ran the code it did not find the next occurrence and run the code, it just found the first occurrence again in column k and ran the copy paste code an additional time.

Km0730 · Feb 12th 2018

Looping Column search based on value then performing code and repeat:

I'll be clear that currently the code has no errors. Currently it finds the first instance in Sheet 5 column K that equals Sheet 4 Cell "F19". Then it runs some copy paste code and ends without going further down column K.

What I need is the code to loop and continue down Column K for all the instances that equal Sheet 4 cell "F19" and repeat the copy past code I have highlighted. Any help or example code is appreciated.

Code

Sub Plan_Review_Monthly_Invoice()
    Application.ScreenUpdating = False
    Dim FindString As String
    Dim Rng As Range
    FindString = Sheet4.Range("F19")
    If Trim(FindString) <> "" Then
        With Sheet5.Range("K16:K4000")
            Set Rng = .Find(What:=FindString, _
                            After:=.Cells(.Cells.Count), _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
            If Not Rng Is Nothing Then
                Application.Goto Rng, True
            
            'Code that must happen in this order before moving to next loop
            ActiveCell.Offset(0, -9).Copy
            Sheet4.Range("A23").PasteSpecial
            ActiveCell.Offset(0, -6).Copy
            Sheet4.Range("B23").PasteSpecial
            ActiveCell.Offset(0, -3).Copy
            Sheet4.Range("C23").PasteSpecial
            ActiveCell.Offset(0, -4).Copy
            Sheet4.Range("D23").PasteSpecial
            ActiveCell.Offset(0, -1).Copy
            Sheet4.Range("E23").PasteSpecial
            Sheet5.Range("J16").Copy
            Sheet4.Range("F23").PasteSpecial Paste:=xlPasteValue
            Sheet4.Range("A23", "H23").Copy
            Sheet4.Range("A26").Rows("1:1").Insert Shift:=xlDown
            ' After this code need to loop code to find next occurance in column K
             
             Else
                MsgBox "No Billable Tasks For Month Requested"
            
            End If
        End With
    End If
End Sub

Display More

Km0730 · Jun 6th 2017

Re: VBA Volitale function controlling rowheight

Thanks,

I think I'm on the write path been editing code but cant get it to work.
Any exact code suggestions?

Km0730 · Jun 6th 2017

Re: VBA Volitale function controlling rowheight

cells(target.row,8) is the command giving the target area for 8 columns over (column H)
Basically the code says if the words "expand" or "decrease" show up in 8 columns over (column H) the row height changes in which row the word is in.

N7 will change by row eg., N7,N8,N9, and will link to the formula = IF(N7=0,"decrease","expand") Currently in Cell H89 but will go down the H column eg.(H89,H90,H91) etc.

It currently works, but only if it is manually put in. It does not auto update when the formula Currently the formula =IF(N7=0,"decrease","expand") is put into any cell in column H.
The row height will change when the formula is initially put in but not after cell N7 changes from 0 to another number or back to 0

Thanks for the response let me know if you need additional information

Km0730 · Jun 6th 2017

Currently the Code works, but only if I enter the data manually into a cell. Currently the cell updates via a formula
I have tried making the application volatile and aplication calculation automatic but it added no difference.

Formula is placed continuously in column H is as follows.
=IF(N7=0,"decrease","expand")

VBA Code is as follows

Code

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
Call Script1(Target)
Call Script2(Target)
End Sub
Private Sub Script1(ByVal Target As Range)
Application.Volatile
Application.Calculation = xlCalculationAutomatic
If Cells(Target.Row, 8).Value = "expand" And Cells(Target.Row, 8).Value = "expand" Then _
    Target.RowHeight = 30




End Sub




Private Sub Script2(ByVal Target As Range)
Application.Volatile
Application.Calculation = xlCalculationAutomatic
If Cells(Target.Row, 8).Value = "decrease" And Cells(Target.Row, 8).Value = "decrease" Then _
    Target.RowHeight = 14.5




End Sub

Display More

As stated before currently the code only works if I manually type "decrease" or "expand" into the cells, not when the formula updates automatically.