Please help

Dear all

I am very new to VBA and I am currently pulling my hair out as I no
longer know what to do with the program that I have written. I would
be eternally grateful to anyone who can show me the light. I have
written a procedure which should look at the only worksheet in my
workbook, and then use the values in column A, to open a designated
account (Excel workbook) then using the values in columns B,C,D update
said worksheet and then move to the next account in column A (The
values in all the columns are different). My problem is that my code
select the different accounts in column and opens them with no
problems, but the values for the data that is posted on all account but
are those from columns B,C,D in row 2 ie the first line and not those
representing the account.

If any of the above is unclear or I can provide any additional
information to help you to assist me please let me know.

My Code is below and I look forward to hearing from you

Kindest Regards

Andrew


Code:
--------------------
Sub withdrawal()
'Declarations
Dim objwb As Workbook
Dim objws As Worksheet
Dim findwhat As Range
Dim rcells As Range
Dim foundcell As Range
Dim foundrow As Integer
Dim rngcashrow As Range
Dim strmfolder As String
Dim strsfolder As String
Dim strmfile As String
Dim straccount As String
Dim strpep As String
Dim strwith As String
Dim curcashval As Currency
Set objwb = Workbooks.Open(Filename:="AndrewN:Users:Reyker:Doc uments:Valuations:PEP dBase", _
ReadOnly:=True)
Set objws = objwb.Worksheets(1)
ActiveWindow.Visible = False
'Userinput
Set findwhat = Sheets(1).Range("a2", Sheets(1).Range("a65536").End(xlUp))
For Each rcells In findwhat
'If findwhat = "" Then End
'Nominal Search Coding
If IsNumeric(rcells) Then
rcells = Left(rcells, 5)
Set foundcell = objws.Columns(1).Find(what:=rcells, after:=objws.Cells(1, 1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
Else
'PEPno Search Coding
rcells = Left(rcells, 4)
'findwhat = Left(findwhat, 4)
Set foundcell = objws.Columns(3).Find(what:=rcells, after:=objws.Cells(1, 3), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
End If
If foundcell Is Nothing Then
MsgBox ("could not find " & findwhat)
Else
foundrow = foundcell.Row
straccount = objws.Cells(foundrow, 1).Value
strmfolder = "AndrewN:Users:Reyker:Documents:Valuations:"
strsfolder = Application.WorksheetFunction.Floor(straccount, 100) & "s:"
strmfile = strmfolder & strsfolder & straccount
If Dir(strmfile) < "" Then
strmfolder = "AndrewN:Users:Reyker:Documents:Valuations:"
strsfolder = Application.WorksheetFunction.Floor(straccount, 100) & "s:"
strmfile = strmfolder & strsfolder & straccount
Workbooks.Open Filename:=strmfile, ReadOnly:=False
End If
End If
strpep = Worksheets("Template").Range("b2").Value
strwith = Worksheets("Template").Range("c2").Value
curcashval = Worksheets("Template").Range("d2").Value
'If strpep = "" Then End
'If strwith = "" Then End
On Error Resume Next
Set rngcashrow = Range("cashBal").End(xlUp).Offset(-1, 0)
On Error Goto 0
If Not rngcashrow Is Nothing Then
rngcashrow.EntireRow.Insert
rngcashrow.Offset(0, -6).Range("b1") = strpep & " withdrawal - " & strwith
rngcashrow.Offset(0, -1).Range("b1") = curcashval
ActiveWorkbook.Close savechanges:=True
End If
Next
'Close PEP dBase
Workbooks("PEP dBase").Close savechanges:=False
end sub
--------------------


--
adncmm1980
------------------------------------------------------------------------
adncmm1980's Profile: http://www.excelforum.com/member.php...o&userid=14965
View this thread: http://www.excelforum.com/showthread...hreadid=265919

That is because you specify to get the data from the

strpep = Worksheets("Template").Range("b2").Value
strwith = Worksheets("Template").Range("c2").Value
curcashval = Worksheets("Template").Range("d2").Value

You would need to adjust those settings to refer to the proper row.

--
Regards,
Tom Ogilvy


"adncmm1980" wrote in message
...

Please help

Dear all

I am very new to VBA and I am currently pulling my hair out as I no
longer know what to do with the program that I have written. I would
be eternally grateful to anyone who can show me the light. I have
written a procedure which should look at the only worksheet in my
workbook, and then use the values in column A, to open a designated
account (Excel workbook) then using the values in columns B,C,D update
said worksheet and then move to the next account in column A (The
values in all the columns are different). My problem is that my code
select the different accounts in column and opens them with no
problems, but the values for the data that is posted on all account but
are those from columns B,C,D in row 2 ie the first line and not those
representing the account.

If any of the above is unclear or I can provide any additional
information to help you to assist me please let me know.

My Code is below and I look forward to hearing from you

Kindest Regards

Andrew


Code:
--------------------
Sub withdrawal()
'Declarations
Dim objwb As Workbook
Dim objws As Worksheet
Dim findwhat As Range
Dim rcells As Range
Dim foundcell As Range
Dim foundrow As Integer
Dim rngcashrow As Range
Dim strmfolder As String
Dim strsfolder As String
Dim strmfile As String
Dim straccount As String
Dim strpep As String
Dim strwith As String
Dim curcashval As Currency
Set objwb =
Workbooks.Open(Filename:="AndrewN:Users:Reyker:Doc uments:Valuations:PEP
dBase", _
ReadOnly:=True)
Set objws = objwb.Worksheets(1)
ActiveWindow.Visible = False
'Userinput
Set findwhat = Sheets(1).Range("a2",
Sheets(1).Range("a65536").End(xlUp))
For Each rcells In findwhat
'If findwhat = "" Then End
'Nominal Search Coding
If IsNumeric(rcells) Then
rcells = Left(rcells, 5)
Set foundcell = objws.Columns(1).Find(what:=rcells,
after:=objws.Cells(1, 1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
Else
'PEPno Search Coding
rcells = Left(rcells, 4)
'findwhat = Left(findwhat, 4)
Set foundcell = objws.Columns(3).Find(what:=rcells,
after:=objws.Cells(1, 3), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
End If
If foundcell Is Nothing Then
MsgBox ("could not find " & findwhat)
Else
foundrow = foundcell.Row
straccount = objws.Cells(foundrow, 1).Value
strmfolder = "AndrewN:Users:Reyker:Documents:Valuations:"
strsfolder = Application.WorksheetFunction.Floor(straccount, 100) & "s:"
strmfile = strmfolder & strsfolder & straccount
If Dir(strmfile) < "" Then
strmfolder = "AndrewN:Users:Reyker:Documents:Valuations:"
strsfolder = Application.WorksheetFunction.Floor(straccount, 100) & "s:"
strmfile = strmfolder & strsfolder & straccount
Workbooks.Open Filename:=strmfile, ReadOnly:=False
End If
End If
strpep = Worksheets("Template").Range("b2").Value
strwith = Worksheets("Template").Range("c2").Value
curcashval = Worksheets("Template").Range("d2").Value
'If strpep = "" Then End
'If strwith = "" Then End
On Error Resume Next
Set rngcashrow = Range("cashBal").End(xlUp).Offset(-1, 0)
On Error Goto 0
If Not rngcashrow Is Nothing Then
rngcashrow.EntireRow.Insert
rngcashrow.Offset(0, -6).Range("b1") = strpep & " withdrawal - " &
strwith
rngcashrow.Offset(0, -1).Range("b1") = curcashval
ActiveWorkbook.Close savechanges:=True
End If
Next
'Close PEP dBase
Workbooks("PEP dBase").Close savechanges:=False
end sub
--------------------


--
adncmm1980
------------------------------------------------------------------------
adncmm1980's Profile:
http://www.excelforum.com/member.php...o&userid=14965
View this thread: http://www.excelforum.com/showthread...hreadid=265919

HI for a start you have the following code lines selected as non
operational is this correct

'If findwhat = "" Then End
'findwhat = Left(findwhat, 4)
'If strpep = "" Then End
'If strwith = "" Then End

BEST N10

"adncmm1980" wrote in message
...

Please help

Dear all

I am very new to VBA and I am currently pulling my hair out as I no
longer know what to do with the program that I have written. I would
be eternally grateful to anyone who can show me the light. I have
written a procedure which should look at the only worksheet in my
workbook, and then use the values in column A, to open a designated
account (Excel workbook) then using the values in columns B,C,D update
said worksheet and then move to the next account in column A (The
values in all the columns are different). My problem is that my code
select the different accounts in column and opens them with no
problems, but the values for the data that is posted on all account but
are those from columns B,C,D in row 2 ie the first line and not those
representing the account.

If any of the above is unclear or I can provide any additional
information to help you to assist me please let me know.

My Code is below and I look forward to hearing from you

Kindest Regards

Andrew


Code:
--------------------
Sub withdrawal()
'Declarations
Dim objwb As Workbook
Dim objws As Worksheet
Dim findwhat As Range
Dim rcells As Range
Dim foundcell As Range
Dim foundrow As Integer
Dim rngcashrow As Range
Dim strmfolder As String
Dim strsfolder As String
Dim strmfile As String
Dim straccount As String
Dim strpep As String
Dim strwith As String
Dim curcashval As Currency
Set objwb =
Workbooks.Open(Filename:="AndrewN:Users:Reyker:Doc uments:Valuations:PEP
dBase", _
ReadOnly:=True)
Set objws = objwb.Worksheets(1)
ActiveWindow.Visible = False
'Userinput
Set findwhat = Sheets(1).Range("a2", Sheets(1).Range("a65536").End(xlUp))
For Each rcells In findwhat
'If findwhat = "" Then End
'Nominal Search Coding
If IsNumeric(rcells) Then
rcells = Left(rcells, 5)
Set foundcell = objws.Columns(1).Find(what:=rcells, after:=objws.Cells(1,
1), _
LookIn:=xlFormulas, lookat:=xlWhole, _
searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
Else
'PEPno Search Coding
rcells = Left(rcells, 4)
'findwhat = Left(findwhat, 4)
Set foundcell = objws.Columns(3).Find(what:=rcells, after:=objws.Cells(1,
3), _
LookIn:=xlValues, lookat:=xlWhole, _
searchorder:=xlByColumns, searchdirection:=xlNext, MatchCase:=False)
End If
If foundcell Is Nothing Then
MsgBox ("could not find " & findwhat)
Else
foundrow = foundcell.Row
straccount = objws.Cells(foundrow, 1).Value
strmfolder = "AndrewN:Users:Reyker:Documents:Valuations:"
strsfolder = Application.WorksheetFunction.Floor(straccount, 100) & "s:"
strmfile = strmfolder & strsfolder & straccount
If Dir(strmfile) < "" Then
strmfolder = "AndrewN:Users:Reyker:Documents:Valuations:"
strsfolder = Application.WorksheetFunction.Floor(straccount, 100) & "s:"
strmfile = strmfolder & strsfolder & straccount
Workbooks.Open Filename:=strmfile, ReadOnly:=False
End If
End If
strpep = Worksheets("Template").Range("b2").Value
strwith = Worksheets("Template").Range("c2").Value
curcashval = Worksheets("Template").Range("d2").Value
'If strpep = "" Then End
'If strwith = "" Then End
On Error Resume Next
Set rngcashrow = Range("cashBal").End(xlUp).Offset(-1, 0)
On Error Goto 0
If Not rngcashrow Is Nothing Then
rngcashrow.EntireRow.Insert
rngcashrow.Offset(0, -6).Range("b1") = strpep & " withdrawal - " &
strwith
rngcashrow.Offset(0, -1).Range("b1") = curcashval
ActiveWorkbook.Close savechanges:=True
End If
Next
'Close PEP dBase
Workbooks("PEP dBase").Close savechanges:=False
end sub
--------------------


--
adncmm1980
------------------------------------------------------------------------
adncmm1980's Profile:
http://www.excelforum.com/member.php...o&userid=14965
View this thread: http://www.excelforum.com/showthread...hreadid=265919



---
Outgoing mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.772 / Virus Database: 519 - Release Date: 01/10/2004