Change range to incorporate multiple columns (not adjacent), VBA/Macro

%3CLINGO-SUB%20id%3D%22lingo-sub-1495123%22%20slang%3D%22en-US%22%3EChange%20range%20to%20incorporate%20multiple%20columns%20(not%20adjacent)%2C%20VBA%2FMacro%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1495123%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20all%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBeen%20playing%20and%20struggling%20with%20this%20for%20some%20time%20now.%20I%20have%20created%20the%20following%20code%20for%20a%20Macro%2C%20and%20although%20I%20have%20it%20working%20for%201%20column%2C%20I'm%20not%20sure%20how%20to%20change%20it%20to%20have%20multiple.%20I%20have%20tried%20doing%20%22K%2CS%2CV%22%20etc%20and%20%22%24K%2C%24S%2C%24V%22%20and%20other%20variables%20and%20I%20can't%20get%20it%20to%20play%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ethe%20code%20I%20have%20atm%20is%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3ESub%20Auto_open()%0A%20%20%20Dim%20r%20As%20Long%0A%20%20%20Dim%20Msg%20As%20String%0A%20%20%20With%20Sheets(%22Data%22)%0A%20%20%20%20%20%20%20For%20r%20%3D%202%20To%205000%0A%20%20%20%20%20%20%20%20%20%20%20If%20.Range(%22K%22%20%26amp%3B%20r).Value%20%26gt%3B%200%20And%20.Range(%22AB1%22).Value%20%26gt%3B%20.Range(%22K%22%20%26amp%3B%20r).Value%20Then%0A%20%20%20%20%20%20%20%20%20%20%20%20%20%20%20Msg%20%3D%20Msg%20%26amp%3B%20vbLf%20%26amp%3B%20.Range(%22B%22%20%26amp%3B%20r).Value%20%26amp%3B%20%22%20requires%20at%20least%20one%20review%22%0A%0A%20%20%20%20%20%20%20%20%20%20%20End%20If%0A%20%20%20%20%20%20%20Next%20r%0A%20%20%20End%20With%0A%20%20%20MsgBox%20Msg%0AEnd%20Sub%0A%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E-%20At%20the%20moment%20the%20range%20is%20set%20to%20column%20%22K%22%2C%20I%20need%20it%20to%20read%2Frecognise%20Columns%2C%20%22K%2C%20S%2C%20V%2C%20X%20and%20AA%22.%3C%2FP%3E%3CP%3E-%20I'm%20not%20sure%20if%20the%20%22r%22%20is%20something%20I%20still%20need%2C%20or%20if%20it%20will%20automatically%20recognise%20it%20as%20a%20column.%3C%2FP%3E%3CP%3E-%20%22AB1%22%20is%20just%20a%20cell%20I%20put%20%22%3DToday()%22%20in%20as%20I%20need%20it%20to%20be%20working%20off%20todays%20date%2C%20and%20I%20wasn't%20sure%20how%20to%20get%20it%20to%20recognise%20it%20without%20a%20reference%20cell.%3C%2FP%3E%3CP%3E-%20All%20of%20these%20will%20eventually%20need%20to%26nbsp%3B%20be%20working%20out%20that%20%22AC%22%20is%20not%20equal%20to%20%22Closed%22%20so%20if%20%22Closed%22%20it%20doesn't%20look%20at%20any%20dates.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1495123%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

Hi all,

 

Been playing and struggling with this for some time now. I have created the following code for a Macro, and although I have it working for 1 column, I'm not sure how to change it to have multiple. I have tried doing "K,S,V" etc and "$K,$S,$V" and other variables and I can't get it to play correctly.

 

the code I have atm is;

Sub Auto_open()
   Dim r As Long
   Dim Msg As String
   With Sheets("Data")
       For r = 2 To 5000
           If .Range("K" & r).Value > 0 And .Range("AB1").Value > .Range("K" & r).Value Then
               Msg = Msg & vbLf & .Range("B" & r).Value & " requires at least one review"

           End If
       Next r
   End With
   MsgBox Msg
End Sub

- At the moment the range is set to column "K", I need it to read/recognise Columns, "K, S, V, X and AA".

- I'm not sure if the "r" is something I still need, or if it will automatically recognise it as a column.

- "AB1" is just a cell I put "=Today()" in as I need it to be working off todays date, and I wasn't sure how to get it to recognise it without a reference cell.

- All of these will eventually need to  be working out that "AC" is not equal to "Closed" so if "Closed" it doesn't look at any dates.

 

0 Replies