how to call a value from a cell into a VB script ?

[raymond m.]

I am writing a VB script for excel, after I paste my data into the document
from a different file,
I have a formula that tells me how many rows are used, and that value is
stored in a cell,
say A1

how do I call that value from a VB script so that I can use it to calculate
an automatic
fill range for the next operation which I have already finished ?

 

[Dave Peterson]

See one alternative at your first post.

but you can use:

dim myVal as Variant 'numbers or text
myval = worksheets("sheet1").range("a1").value

 

[raymond m.]

hahaha,

great, I like VB, learning more and more of it everyday

 

[RompStar]

can you help me with the last part ?

here is my script,

start here --------

Sub SelectiveFormatPopUp()

' highlight anything < 0 red, > 2 Red, [ 0,1,2 Black ]

Dim c As Range
Dim myVal As Variant 'numbers or text

myVal = Worksheets("Master").Range("n8").Value

On Error GoTo Finish

Set c = Range("n11:myVal") <------- not right, can you help me out
with this ?

For Each Item In c
If Val(Item) > 2 Or Val(Item) < 0 Then
Item.Font.Bold = True
Item.Font.COLOR = vbRed
Else
Item.Font.Bold = True
Item.Font.COLOR = vbBlack
End If
Next Item

Finish:
End Sub

 

[Dave Peterson]

One way:

Option Explicit
Sub SelectiveFormatPopUp()

' highlight anything < 0 red, > 2 Red, [ 0,1,2 Black ]

Dim c As Range
Dim myCell As Range
Dim myVal As Variant 'numbers or text

myVal = Worksheets("Master").Range("n8").Value

On Error GoTo Finish

'If it's just column N
Set c = Range("n11:N" & myVal)
'or
'Set c = Range("n11:X" & myVal)
'if you want N11:X (whatever is in range("N8"))


For Each myCell In c
If Val(myCell.Value) > 2 _
Or Val(myCell.Value) < 0 Then
myCell.Font.Bold = True
myCell.Font.Color = vbRed
Else
myCell.Font.Bold = True
myCell.Font.Color = vbBlack
End If
Next myCell

Finish:
End Sub

This line:
Set c = Range("n11:N" & myVal)
points at the activesheet. I like to be more explicit and make sure I specify
the sheet.

Set c = activesheet.Range("n11:N" & myVal)

I could also point at a different sheet:

Set c = worksheets("sheet99").Range("n11:N" & myVal)

===
I also changed Item to myCell. VBA has its own Item keyword. And I like to
stay away from those when I use my own variables.

can you help me with the last part ?

here is my script,

start here --------

Sub SelectiveFormatPopUp()

' highlight anything < 0 red, > 2 Red, [ 0,1,2 Black ]

Dim c As Range
Dim myVal As Variant 'numbers or text

myVal = Worksheets("Master").Range("n8").Value

On Error GoTo Finish

Set c = Range("n11:myVal") <------- not right, can you help me out
with this ?

For Each Item In c
If Val(Item) > 2 Or Val(Item) < 0 Then
Item.Font.Bold = True
Item.Font.COLOR = vbRed
Else
Item.Font.Bold = True
Item.Font.COLOR = vbBlack
End If
Next Item

Finish:
End Sub

 

[Dave Peterson]

Ps. You had one reply at your other post that showed how to get that last row
without using a function in the worksheet.

can you help me with the last part ?

here is my script,

start here --------

Sub SelectiveFormatPopUp()

' highlight anything < 0 red, > 2 Red, [ 0,1,2 Black ]

Dim c As Range
Dim myVal As Variant 'numbers or text

myVal = Worksheets("Master").Range("n8").Value

On Error GoTo Finish

Set c = Range("n11:myVal") <------- not right, can you help me out
with this ?

For Each Item In c
If Val(Item) > 2 Or Val(Item) < 0 Then
Item.Font.Bold = True
Item.Font.COLOR = vbRed
Else
Item.Font.Bold = True
Item.Font.COLOR = vbBlack
End If
Next Item

Finish:
End Sub

 

[RompStar]

what do yhou think ?

Sub SelectiveFormatPopUp()

' then it will highlight anything < 0 red, > 2 Red, [ 0,1,2 Black ]

Dim c As Range

Dim LastRow As Long
LastRow = Range("N11").End(xlDown).Row

On Error GoTo Finish

' new set doesn't ask for range, it knows the range

Set c = Range("N11:N" & LastRow)

For Each Item In c
If Val(Item) > 2 Or Val(Item) < 0 Then
Item.Font.Bold = True
Item.Font.COLOR = vbRed
Else
Item.Font.Bold = True
Item.Font.COLOR = vbBlack
End If
Next Item

Finish:
End Sub

 

[Dave Peterson]

I still wouldn't use Item. It may not confuse excel/vba, but it can confuse me.

And I usually start at the bottom and work my way up (but if you know your data,
it's more of a personal preference).

And I still like fully qualifying my ranges.

But these are pretty picky comments. But you asked! <vbg>.

what do yhou think ?

Sub SelectiveFormatPopUp()

' then it will highlight anything < 0 red, > 2 Red, [ 0,1,2 Black ]

Dim c As Range

Dim LastRow As Long
LastRow = Range("N11").End(xlDown).Row

On Error GoTo Finish

' new set doesn't ask for range, it knows the range

Set c = Range("N11:N" & LastRow)

For Each Item In c
If Val(Item) > 2 Or Val(Item) < 0 Then
Item.Font.Bold = True
Item.Font.COLOR = vbRed
Else
Item.Font.Bold = True
Item.Font.COLOR = vbBlack
End If
Next Item

Finish:
End Sub

 

[RompStar]

what would you use instead of item ?

here is how the script looks:

Sub SelectiveFormatandfillformula()

Dim c As Range
Dim LastRow As Long
LastRow = Range("N11").End(xlDown).Row

On Error GoTo Finish

Range("N11:N" & Range("a65536").End(xlUp).Row).Formula _
= "=NETWORKDAYS(J11,K11)-1"

Set c = Range("N11:N" & LastRow)

For Each Item In c
If Val(Item) > 2 Or Val(Item) < 0 Then
Item.Font.Bold = True
Item.Font.COLOR = vbRed
Else
Item.Font.Bold = True
Item.Font.COLOR = vbBlack
End If
Next Item

Finish:
End Sub

 

[Dave Peterson]

I declared a variable called myCell in my other post.

And it looks like you're not using LastRow anymore.

 

[RompStar]

ok, I'll take a look at it over the weekend, stupid google cuts off my
text.. that's why it's called beta I guess :- )