VBA- Variable declaration help

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

Code:

---

    Dim wsf As WorksheetFunction

---

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

Code:

---

Dim wsf As WorksheetFunction

---

Code:

---

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

---

Artik

Hi Artik and Paulked.

Thanks for your help its working.:thumb


Regards,
mg

Code:

---

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

---

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

Code:

---

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

Quote:

---

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

---

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

Code:

---

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

---

Code:

---

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

---

:devil2:

I'd use another pejorative :whistle:

Code:

---

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

---

:think::reading::type:wine::clap2:

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

Code:

---

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)

Code:

---

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

---

"pejorative"?

See: https://latin-dictionary.net/definit...va-peiorativum

Different, isn't it.
https://www.merriam-webster.com/dictionary/pejorative

Quote:

---

Originally Posted by snb

See: https://latin-dictionary.net/definit...va-peiorativum

---

Infinite Loop?

"pejorative" --> "periorative" -->"pejorative" --> "periorative" --> "pejorative" --> "periorative" --> ......