PDA

View Full Version : [SOLVED] 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.:thumb


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
:devil2:

snb
11-07-2019, 04:57 AM
I'd use another pejorative :whistle:


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

paulked
11-07-2019, 05:31 AM
:think::reading::type:wine::clap2:

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

jolivanes
11-07-2019, 09:43 PM
"pejorative"?

snb
11-08-2019, 04:18 AM
See: https://latin-dictionary.net/definition/29639/peiorativus-peiorativa-peiorativum

jolivanes
11-08-2019, 08:11 AM
Different, isn't it.
https://www.merriam-webster.com/dictionary/pejorative (https://www.merriam-webster.com/dictionary/pejorative)

Paul_Hossler
11-08-2019, 08:19 AM
See: https://latin-dictionary.net/definition/29639/peiorativus-peiorativa-peiorativum


Infinite Loop?

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