Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Vaiable Help in VBA
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default Vaiable Help in VBA
I need help with a vaiable issue.........See below in caps for issue. Thanks
for any help, I am getting a headach tring to figuire this one out!

Sub RecapReport()

Sheets("Recap Report").Select
Dim v As Variant, bk As Workbook, sh As Worksheet, ws As Worksheet
Dim bk1 As Workbook, sh1 As Worksheet
Dim sn As String, sm As String, sl As String, i As Long
Dim rng1 As Range, rng As Range
Dim rng2 As Range
Set bk = Workbooks("Recaps08.xls")
Set sh = bk.Worksheets("Data")
Set ws = bk.Worksheets("Recap Report")
sn = LCase(sh.Range("D3").Value)
sm = sh.Range("D4").Value
sl = sh.Range("D5").Value
If sn = "all" Then

THIS IS THE LINE I NEED TO CHANGE TO A VAIABLE PENDING EACH CELL IN COLUMN B
FROM WORKSHEET "Data" THAT HAS AN "X" IN IT, WILL THEN GET THE NAME FROM
COLUMN A. IF A CELL IN COLUMN B DOES NOT HAVE AN "X" THEN NEXT UNTILL 5
CELLS IN A ROW IN COLUMN B = "".
v = Array("Tony Sarullo " & sl & ".xls", "John Mudaro " & sl & ".xls", "Ron Ficarelli " & sl & ".xls")<<<<<<<

Else
v = Array(sn & " " & sl)
End If
For i = LBound(v) To UBound(v)
Set bk1 = Workbooks.Open("C:\Service Recaps\" & v(i))
Set sh1 = bk1.Worksheets(sm)
If i = LBound(v) Then
Set rng1 = ws.Range(ws.Range("A7"), ws.Cells(Rows.Count, 1).End(xlUp))
rng1.EntireRow.Delete
Set rng = ws.Range("A7")
Else
Set rng = ws.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Set rng2 = sh1.Range(sh1.Range("A9"), _
sh1.Cells(Rows.Count, 1).End(xlUp)).EntireRow
rng2.Copy Destination:=rng
bk1.Close SaveChanges:=False
Next
'LastRow = Range("A65536").End(xlUp).Row
Application.Run "SortReport"
Application.Run "Addtotals"
End Sub
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 703
Default Vaiable Help in VBA
Hi

I think this should do it:

Dim FirstCell As Range
Dim LastCell As Range
Dim xCount As Long

If sn = "all" Then
Set FirstCell = sh.Range("B2")
Set LastCell = sh.Range("B" & Rows.Count).End(xlUp)
For Each cell In Range(FirstCell, LastCell)
If cell.Value = "X" Then
ReDim Preserve v(xCount)
v(xCount) = cell.Offset(0, -1).Value & sl & ".xls"
xCount = xCount + 1
End If
Next

Regards,
Per

On 12 Dec., 18:26, ParTeeGolfer
wrote:
I need help with a vaiable issue.........See below in caps for issue. Thanks
for any help, I am getting a headach tring to figuire this one out!

Sub RecapReport()

Sheets("Recap Report").Select
* * Dim v As Variant, bk As Workbook, sh As Worksheet, ws As Worksheet
* * Dim bk1 As Workbook, sh1 As Worksheet
* * Dim sn As String, sm As String, sl As String, i As Long
* * Dim rng1 As Range, rng As Range
* * Dim rng2 As Range
Set bk = Workbooks("Recaps08.xls")
Set sh = bk.Worksheets("Data")
Set ws = bk.Worksheets("Recap Report")
* * sn = LCase(sh.Range("D3").Value)
* * sm = sh.Range("D4").Value
* * sl = sh.Range("D5").Value
If sn = "all" Then

THIS IS THE LINE I NEED TO CHANGE TO A VAIABLE PENDING EACH CELL IN COLUMN B
FROM WORKSHEET "Data" THAT HAS AN "X" IN IT, WILL THEN GET THE NAME FROM
COLUMN *A. IF A CELL IN COLUMN B DOES NOT HAVE AN "X" THEN NEXT UNTILL 5
CELLS IN A ROW IN COLUMN B = "".

v = Array("Tony Sarullo " & sl & ".xls", "John Mudaro " & sl & ".xls", "Ron Ficarelli " & sl & ".xls")<<<<<<<

* * Else
* * v = Array(sn & " " & sl)
* * End If
For i = LBound(v) To UBound(v)
* * Set bk1 = Workbooks.Open("C:\Service Recaps\" & v(i))
* * Set sh1 = bk1.Worksheets(sm)
* * If i = LBound(v) Then
* * Set rng1 = ws.Range(ws.Range("A7"), ws.Cells(Rows.Count, 1).End(xlUp))
* * rng1.EntireRow.Delete
* * Set rng = ws.Range("A7")
Else
* * Set rng = ws.Cells(Rows.Count, 1).End(xlUp)(2)
End If
* * Set rng2 = sh1.Range(sh1.Range("A9"), _
* * sh1.Cells(Rows.Count, 1).End(xlUp)).EntireRow
* * rng2.Copy Destination:=rng
* * bk1.Close SaveChanges:=False
Next
'LastRow = Range("A65536").End(xlUp).Row
Application.Run "SortReport"
Application.Run "Addtotals"
End Sub

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default Vaiable Help in VBA
I tried the code as you suggested however I get a Runtime error 13 Type
mismatch in the line with: For i = LBound(v) To UBound(v)




"Per Jessen" wrote:

Hi

I think this should do it:

Dim FirstCell As Range
Dim LastCell As Range
Dim xCount As Long

If sn = "all" Then
Set FirstCell = sh.Range("B2")
Set LastCell = sh.Range("B" & Rows.Count).End(xlUp)
For Each cell In Range(FirstCell, LastCell)
If cell.Value = "X" Then
ReDim Preserve v(xCount)
v(xCount) = cell.Offset(0, -1).Value & sl & ".xls"
xCount = xCount + 1
End If
Next

Regards,
Per

On 12 Dec., 18:26, ParTeeGolfer
wrote:
I need help with a vaiable issue.........See below in caps for issue. Thanks
for any help, I am getting a headach tring to figuire this one out!

Sub RecapReport()

Sheets("Recap Report").Select
Dim v As Variant, bk As Workbook, sh As Worksheet, ws As Worksheet
Dim bk1 As Workbook, sh1 As Worksheet
Dim sn As String, sm As String, sl As String, i As Long
Dim rng1 As Range, rng As Range
Dim rng2 As Range
Set bk = Workbooks("Recaps08.xls")
Set sh = bk.Worksheets("Data")
Set ws = bk.Worksheets("Recap Report")
sn = LCase(sh.Range("D3").Value)
sm = sh.Range("D4").Value
sl = sh.Range("D5").Value
If sn = "all" Then

THIS IS THE LINE I NEED TO CHANGE TO A VAIABLE PENDING EACH CELL IN COLUMN B
FROM WORKSHEET "Data" THAT HAS AN "X" IN IT, WILL THEN GET THE NAME FROM
COLUMN A. IF A CELL IN COLUMN B DOES NOT HAVE AN "X" THEN NEXT UNTILL 5
CELLS IN A ROW IN COLUMN B = "".

v = Array("Tony Sarullo " & sl & ".xls", "John Mudaro " & sl & ".xls", "Ron Ficarelli " & sl & ".xls")<<<<<<<

Else
v = Array(sn & " " & sl)
End If
For i = LBound(v) To UBound(v)
Set bk1 = Workbooks.Open("C:\Service Recaps\" & v(i))
Set sh1 = bk1.Worksheets(sm)
If i = LBound(v) Then
Set rng1 = ws.Range(ws.Range("A7"), ws.Cells(Rows.Count, 1).End(xlUp))
rng1.EntireRow.Delete
Set rng = ws.Range("A7")
Else
Set rng = ws.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Set rng2 = sh1.Range(sh1.Range("A9"), _
sh1.Cells(Rows.Count, 1).End(xlUp)).EntireRow
rng2.Copy Destination:=rng
bk1.Close SaveChanges:=False
Next
'LastRow = Range("A65536").End(xlUp).Row
Application.Run "SortReport"
Application.Run "Addtotals"
End Sub


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 703
Default Vaiable Help in VBA
Hi

My fault :-(, forgot that Dim V as Variant shoud be changed to:

Dim V() as Variant 'Indicating that V is an array

Regards,
Per

On 12 Dec., 20:11, ParTeeGolfer
wrote:
I tried the code as you suggested however I get a Runtime error 13 Type
mismatch in the line with: For i = LBound(v) To UBound(v)



"Per Jessen" wrote:
Hi

I think this should do it:

*Dim FirstCell As Range
*Dim LastCell As Range
*Dim xCount As Long

If sn = "all" Then
* * Set FirstCell = sh.Range("B2")
* * Set LastCell = sh.Range("B" & Rows.Count).End(xlUp)
* * For Each cell In Range(FirstCell, LastCell)
* * * * If cell.Value = "X" Then
* * * * * * ReDim Preserve v(xCount)
* * * * * * v(xCount) = cell.Offset(0, -1).Value & sl & "..xls"
* * * * * * xCount = xCount + 1
* * * * End If
* * Next

Regards,
Per

On 12 Dec., 18:26, ParTeeGolfer
wrote:
I need help with a vaiable issue.........See below in caps for issue. Thanks
for any help, I am getting a headach tring to figuire this one out!

Sub RecapReport()

Sheets("Recap Report").Select
* * Dim v As Variant, bk As Workbook, sh As Worksheet, ws As Worksheet
* * Dim bk1 As Workbook, sh1 As Worksheet
* * Dim sn As String, sm As String, sl As String, i As Long
* * Dim rng1 As Range, rng As Range
* * Dim rng2 As Range
Set bk = Workbooks("Recaps08.xls")
Set sh = bk.Worksheets("Data")
Set ws = bk.Worksheets("Recap Report")
* * sn = LCase(sh.Range("D3").Value)
* * sm = sh.Range("D4").Value
* * sl = sh.Range("D5").Value
If sn = "all" Then

THIS IS THE LINE I NEED TO CHANGE TO A VAIABLE PENDING EACH CELL IN COLUMN B
FROM WORKSHEET "Data" THAT HAS AN "X" IN IT, WILL THEN GET THE NAME FROM
COLUMN *A. IF A CELL IN COLUMN B DOES NOT HAVE AN "X" THEN NEXT UNTILL 5
CELLS IN A ROW IN COLUMN B = "".

v = Array("Tony Sarullo " & sl & ".xls", "John Mudaro " & sl & ".xls", "Ron Ficarelli " & sl & ".xls")<<<<<<<

* * Else
* * v = Array(sn & " " & sl)
* * End If
For i = LBound(v) To UBound(v)
* * Set bk1 = Workbooks.Open("C:\Service Recaps\" & v(i))
* * Set sh1 = bk1.Worksheets(sm)
* * If i = LBound(v) Then
* * Set rng1 = ws.Range(ws.Range("A7"), ws.Cells(Rows.Count, 1)..End(xlUp))
* * rng1.EntireRow.Delete
* * Set rng = ws.Range("A7")
Else
* * Set rng = ws.Cells(Rows.Count, 1).End(xlUp)(2)
End If
* * Set rng2 = sh1.Range(sh1.Range("A9"), _
* * sh1.Cells(Rows.Count, 1).End(xlUp)).EntireRow
* * rng2.Copy Destination:=rng
* * bk1.Close SaveChanges:=False
Next
'LastRow = Range("A65536").End(xlUp).Row
Application.Run "SortReport"
Application.Run "Addtotals"
End Sub- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 22
Default Vaiable Help in VBA
Thanks Per, This worked out great.............. Thanks for your help

"Per Jessen" wrote:

Hi

My fault :-(, forgot that Dim V as Variant shoud be changed to:

Dim V() as Variant 'Indicating that V is an array

Regards,
Per

On 12 Dec., 20:11, ParTeeGolfer
wrote:
I tried the code as you suggested however I get a Runtime error 13 Type
mismatch in the line with: For i = LBound(v) To UBound(v)



"Per Jessen" wrote:
Hi

I think this should do it:

Dim FirstCell As Range
Dim LastCell As Range
Dim xCount As Long

If sn = "all" Then
Set FirstCell = sh.Range("B2")
Set LastCell = sh.Range("B" & Rows.Count).End(xlUp)
For Each cell In Range(FirstCell, LastCell)
If cell.Value = "X" Then
ReDim Preserve v(xCount)
v(xCount) = cell.Offset(0, -1).Value & sl & "..xls"
xCount = xCount + 1
End If
Next

Regards,
Per

On 12 Dec., 18:26, ParTeeGolfer
wrote:
I need help with a vaiable issue.........See below in caps for issue. Thanks
for any help, I am getting a headach tring to figuire this one out!

Sub RecapReport()

Sheets("Recap Report").Select
Dim v As Variant, bk As Workbook, sh As Worksheet, ws As Worksheet
Dim bk1 As Workbook, sh1 As Worksheet
Dim sn As String, sm As String, sl As String, i As Long
Dim rng1 As Range, rng As Range
Dim rng2 As Range
Set bk = Workbooks("Recaps08.xls")
Set sh = bk.Worksheets("Data")
Set ws = bk.Worksheets("Recap Report")
sn = LCase(sh.Range("D3").Value)
sm = sh.Range("D4").Value
sl = sh.Range("D5").Value
If sn = "all" Then

THIS IS THE LINE I NEED TO CHANGE TO A VAIABLE PENDING EACH CELL IN COLUMN B
FROM WORKSHEET "Data" THAT HAS AN "X" IN IT, WILL THEN GET THE NAME FROM
COLUMN A. IF A CELL IN COLUMN B DOES NOT HAVE AN "X" THEN NEXT UNTILL 5
CELLS IN A ROW IN COLUMN B = "".

v = Array("Tony Sarullo " & sl & ".xls", "John Mudaro " & sl & ".xls", "Ron Ficarelli " & sl & ".xls")<<<<<<<

Else
v = Array(sn & " " & sl)
End If
For i = LBound(v) To UBound(v)
Set bk1 = Workbooks.Open("C:\Service Recaps\" & v(i))
Set sh1 = bk1.Worksheets(sm)
If i = LBound(v) Then
Set rng1 = ws.Range(ws.Range("A7"), ws.Cells(Rows.Count, 1)..End(xlUp))
rng1.EntireRow.Delete
Set rng = ws.Range("A7")
Else
Set rng = ws.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Set rng2 = sh1.Range(sh1.Range("A9"), _
sh1.Cells(Rows.Count, 1).End(xlUp)).EntireRow
rng2.Copy Destination:=rng
bk1.Close SaveChanges:=False
Next
'LastRow = Range("A65536").End(xlUp).Row
Application.Run "SortReport"
Application.Run "Addtotals"
End Sub- Skjul tekst i anførselstegn -

- Vis tekst i anførselstegn -


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35,218
Default Vaiable Help in VBA
You didn't like the suggestion you got a week ago?

ParTeeGolfer wrote:

I need help with a vaiable issue.........See below in caps for issue. Thanks
for any help, I am getting a headach tring to figuire this one out!

Sub RecapReport()

Sheets("Recap Report").Select
Dim v As Variant, bk As Workbook, sh As Worksheet, ws As Worksheet
Dim bk1 As Workbook, sh1 As Worksheet
Dim sn As String, sm As String, sl As String, i As Long
Dim rng1 As Range, rng As Range
Dim rng2 As Range
Set bk = Workbooks("Recaps08.xls")
Set sh = bk.Worksheets("Data")
Set ws = bk.Worksheets("Recap Report")
sn = LCase(sh.Range("D3").Value)
sm = sh.Range("D4").Value
sl = sh.Range("D5").Value
If sn = "all" Then

THIS IS THE LINE I NEED TO CHANGE TO A VAIABLE PENDING EACH CELL IN COLUMN B
FROM WORKSHEET "Data" THAT HAS AN "X" IN IT, WILL THEN GET THE NAME FROM
COLUMN A. IF A CELL IN COLUMN B DOES NOT HAVE AN "X" THEN NEXT UNTILL 5
CELLS IN A ROW IN COLUMN B = "".
v = Array("Tony Sarullo " & sl & ".xls", "John Mudaro " & sl & ".xls", "Ron Ficarelli " & sl & ".xls")<<<<<<<

Else
v = Array(sn & " " & sl)
End If
For i = LBound(v) To UBound(v)
Set bk1 = Workbooks.Open("C:\Service Recaps\" & v(i))
Set sh1 = bk1.Worksheets(sm)
If i = LBound(v) Then
Set rng1 = ws.Range(ws.Range("A7"), ws.Cells(Rows.Count, 1).End(xlUp))
rng1.EntireRow.Delete
Set rng = ws.Range("A7")
Else
Set rng = ws.Cells(Rows.Count, 1).End(xlUp)(2)
End If
Set rng2 = sh1.Range(sh1.Range("A9"), _
sh1.Cells(Rows.Count, 1).End(xlUp)).EntireRow
rng2.Copy Destination:=rng
bk1.Close SaveChanges:=False
Next
'LastRow = Range("A65536").End(xlUp).Row
Application.Run "SortReport"
Application.Run "Addtotals"
End Sub

--

Dave Peterson
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Vaiable in range().formula Sune Fibaek Excel Programming 4 November 6th 06 06:40 PM
use vaiable for all sheets R..VENKATARAMAN Excel Programming 5 October 4th 06 04:50 AM
Assign vaiable to a cell - gold gold gold via OfficeKB.com Excel Worksheet Functions 1 June 4th 05 04:31 AM
Clearing a date vaiable PSKelligan Excel Programming 0 April 27th 05 11:39 PM
Clearing a date vaiable Alok Excel Programming 1 April 27th 05 10:21 PM


All times are GMT +1. The time now is 09:29 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement