VBA- Variable declaration help

[malleshg24]

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]

Dim wsf As WorksheetFunction



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

[Artik]

Dim wsf As WorksheetFunction
Range("C3:C8,E3:E8,G3:G8,...").Value = "-"
Artik

[malleshg24]

Hi Artik and Paulked.

Thanks for your help its working.:thumb


Regards,
mg

[paulked]

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]

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]

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]

:devil2:

[snb]

I'd use another pejorative :whistle:


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

[paulked]

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

[Paul_Hossler]

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

[jolivanes]

"pejorative"?

[snb]

See: https://latin-dictionary.net/definition/29639/peiorativus-peiorativa-peiorativum

[jolivanes]

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

[Paul_Hossler]

See: https://latin-dictionary.net/definition/29639/peiorativus-peiorativa-peiorativum


Infinite Loop?

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