I have tried to create VB. but i can't get it to work could someone help me with this?
I have created the one below but think there might be to many lines maybe around 1000 lines like that. hoping someone could help me.
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim xlRow As Integer
Dim x1Column As Integer
Dim CTY As Integer
Dim TEXT As String
Dim PRICE As String
Dim ACCOUNT As String
Dim NO_OF As String
Dim Count
xlRow = 1
Count = 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
'If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
'xlRow = xlRow + 1
Well you did not tell us what you are really trying to do nor what problem you are having but you do not have any loop in your code so I would assume that is why there is a problem with looping or rather not looping.
Not sure what you mean by does not work? If the lines worked then that should work in a loop as well as it really is no different.
The only thing I see your code doing in the loop is skipping a row when the IF test is true, nothing is being done with the data so I have no idea what you are trying to do nor what problem you are experiencing.
The lines in the first post do not do anything either. They are all commented out and if the comments are removed would trigger errors as you have block IF without matching End IF
Below is the VB the makro i have created with didn't work. what I did is instead a a loop have created 100 linies with looks like this:
If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
xlRow = xlRow + 1
This is the only way i can make it find the data i want. the loop does not find any data just close the makro without putting any thing in.
'----------------------------------------------------------------
Sub tese()
With Session
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim strExcelFileName As String
strExcelFileName = "C:\test.xlsx"
Set objExcel = New Excel.Application
objExcel.Visible = False
objExcel.UseSystemSeparators = False
Set objWorkbook = objExcel.Workbooks.Open(strExcelFileName)
Set objWorksheet = objWorkbook.Sheets("Pivot")
Dim xlRow As Integer
Dim x1Column As Integer
Dim CTY As Integer
Dim TEXT As String
Dim PRICE As String
Dim ACCOUNT As String
Dim NO_OF As String
Dim Count
CTY = InputBox("Country?")
If CTY = 678 Then
xlRow = 1
Count = 1
Do While xlRow < 500
If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
xlRow = xlRow + 1
End If
xlRow = xlRow + 1
Loop
Do While objWorksheet.Range("A" & xlRow).Value Like "678*"
TEXT = objWorksheet.Range("A" & xlRow).TEXT
PRICE = objWorksheet.Range("B" & xlRow).TEXT
ACCOUNT = objWorksheet.Range("C" & xlRow).Value
NO_OF = objWorksheet.Range("D" & xlRow).Value
You need to set a reference to the Ms Excel 12.0 library
Code:
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim strExcelFileName As String
Private Sub Command1_Click()
strExcelFileName = "C:\test.xlsx"
Set objExcel = New Excel.Application
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Open(strExcelFileName)
Set objWorksheet = objWorkbook.Sheets("Sheet1")' Chage this to "pivot1"
For n = 1 To 500
If objWorksheet.Range("A" & n).Value = "678" Then
MsgBox "Line " & n & " is the value you are looking for"
Exit For
End If
Next n
End Sub
Do While xlRow < 500
If Not objWorksheet.Range("A" & xlRow).Value Like "678*" Then
xlRow = xlRow + 1
End If
xlRow = xlRow + 1
Loop
Your loop is not doing anything other than setting xlRow to 500 0r 501
You have to place the code to do what you want to do within the loop if you expect something to be done.
Also as coded your loop will skip a line everytime the If Statement is true as it will add in the If statement and add after the If statement so when it is true it actually adds 2 and when false it will add 1 but that is the only thing it is doing.
If your second loop xlRow has a starting value of 500 or 501 and you are incrementing that value again.
I do not normally program in Excel so I am not sure about the range statements or a couple of others you have there but that loop structure is way off.
Last edited by DataMiser; February 21st, 2011 at 08:05 AM.
You need to set a reference to the Ms Excel 12.0 library
Code:
Dim objExcel As Excel.Application
Dim objWorkbook As Excel.Workbook
Dim objWorksheet As Excel.Worksheet
Dim strExcelFileName As String
Private Sub Command1_Click()
strExcelFileName = "C:\test.xlsx"
Set objExcel = New Excel.Application
objExcel.Visible = False
Set objWorkbook = objExcel.Workbooks.Open(strExcelFileName)
Set objWorksheet = objWorkbook.Sheets("Sheet1")' Chage this to "pivot1"
For n = 1 To 500
If objWorksheet.Range("A" & n).Value = "678" Then
MsgBox "Line " & n & " is the value you are looking for"
Exit For
End If
Next n
End Sub
looping problem VB
Reply With Quote
If your question has been answered satisfactorily, and it has been helpful, then, please, Rate this Post!
Originally Posted by dglienna
Re: looping problem VB
Bookmarks