Need help with Dynamic range looking for a value and checking the values in the row for any value that isn't blank

becca51178

Board Regular
Joined
Feb 19, 2012
Messages
64
I need a VBA macro that will check the values in a column for "NFR, if it finds it then I need it check all the values on the same row
Range(0, 3) - Range(0,6) and any of them that is not blank I want to create a worksheet with the value
This is what I have so far


With ActiveSheet
lRow = .Cells(.Rows.count, 1).End(xlUp).Row
lCol = .Cells(13, .Columns.count).End(xlToLeft).Column


For Each rng In Range("K13:K" & lRow)
If rng.Value = "NFR" Then
 

Some videos you may like

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

onlyadrafter

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
Hello,

VBA Code:
Sub ADD_SHEET()
        MY_SOURCE = ActiveSheet.Name
        For MY_ROWS = 13 To Range("K" & Rows.Count).End(xlUp).Row
            If Range("K" & MY_ROWS).Value = "NFR" Then
                For MY_COLS = 3 To 6
                    If Not IsEmpty(Cells(MY_ROWS, MY_COLS)) Then
                        MY_NAME = Cells(MY_ROWS, MY_COLS).Value
                        Worksheets.Add After:=Sheets(ActiveWorkbook.Sheets.Count)
                        ActiveSheet.Name = MY_NAME
                        Sheets(MY_SOURCE).Select
                    End If
                Next MY_COLS
            End If
        Next MY_ROWS
End Sub

Have used column K to check for NFR, and checking cols C, D, E and F to search for possible filenames. The code doesn't have any error checking, so if repeated sheet names, you will have a problem.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,233
Messages
5,594,964
Members
413,955
Latest member
FalcoDaz

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top