VBA- Variable declaration help

**malleshg24** · 11-06-2019, 06:39 PM

Hi Team,

'Help1 - Need help in declaring variable for Application.worksheetfunction.

Dim wsf As object or Variant
Set wsf = Application.WorksheetFunction

With wsf
wsf. 'not getting help after pressing wsf.
end with

Help2 - Can we shorten the code below in a single line

Range("c3:c8").value ="-"
Range("e3:e8").value ="-"
Range("g3:g8").value ="-"
Range("I3:I8").value ="-"
Range("K3:K8").value ="-"
Range("M3:M8").value ="-"
Range("N3:N8").value ="-"and so on till Till Range("v3:v8")

Thanks for your help in advance!

Regards
mg

**paulked** · 11-06-2019, 06:58 PM

    Dim wsf As WorksheetFunction

Use two nested loops, one for 3 to 8 and one for c to v.

**Artik** · 11-06-2019, 06:59 PM

Dim wsf As WorksheetFunction

Range("C3:C8,E3:E8,G3:G8,...").Value = "-"

Artik

**malleshg24** · 11-06-2019, 07:03 PM

Hi Artik and Paulked.

Thanks for your help its working.

Regards,
mg

**paulked** · 11-06-2019, 07:04 PM

Sub test()
    Dim rw As Long, cl As Long
    For rw = 3 To 8
        For cl = 3 To 22
            Cells(rw, cl) = "-"
        Next
    Next
End Sub

**Artik** · 11-06-2019, 07:22 PM

If the spacing between ranges is not regular and you want to do it in a loop, you can do it this way:

Sub AAA()
  Dim i As Long
  Dim varArr As Variant
  
  varArr = Split("C3,E3,G3,I3,K3,M3,N3,Q3,R3,V3", ",")
  
  For i = 0 To UBound(varArr)
      Range(varArr(i)).Resize(5).Value = "-"
  Next i
  
End Sub

Artik

**SamT** · 11-07-2019, 04:14 AM

and so on till Till Range("v3:v8")

Well... That's a broken pattern, so there is no "and so on."

Function MeLazy() AS Boolean
     MeLazy = PauldKed + Artik
End Function

Sub test()
    Dim cl As Long

'Step 2 = every other column (up to, and including,  u)
    For cl = 3 To 21 Step 2
         Cells(3, cl).Resize(5).Text = "-"
   Next
End Sub

**paulked** · 11-07-2019, 04:48 AM

**snb** · 11-07-2019, 04:57 AM

I'd use another pejorative

Sub M_snb()
   [C3:V8] = [if(mod(column(c3:V3),2)=1,"-",C3:V8)]
End Sub

**paulked** · 11-07-2019, 05:31 AM

**Paul_Hossler** · 11-07-2019, 09:47 AM

There's a break in the pattern -- is that correct?

Range("c3:c8").value ="-"
Range("e3:e8").value ="-"
Range("g3:g8").value ="-"
Range("I3:I8").value ="-"
Range("K3:K8").value ="-"
Range("M3:M8").value ="-"

'pattern break

Range("N3:N8").value ="-"and so on till Till Range("v3:v8")

Part 1: C-E-G-I-K-M (from C up by 2 columns)

Part 2: N-P-R-T-V (from N up by 2 columns)

Option Explicit

Sub test()
    Dim wsf As WorksheetFunction
    Dim c As Long


    Set wsf = Application.WorksheetFunction


    For c = 3 To 13 Step 2
        Cells(3, c).Resize(6, 1).Value = "-"
    Next c
    For c = 14 To 22 Step 2
        Cells(3, c).Resize(6, 1).Value = "-"
    Next c
    
End Sub