Newbie attempting VBA Scripting

[pogster]

Joel,

Im having some trouble implementing the code. I can't seem to get it
to work with the following code, what am i doing wrong?

Sub VariableColumns()

Application.ScreenUpdating = False
Worksheets.Add after:=Sheets(Sheets.Count)
Set NewSheet = ActiveSheet
ActiveSheet.Name = "Adjust Test"


With Worksheets("Premiums Commissions")

Set BU = Rows(1).Find(what:="Affiliate Code",
LookIn:=xlValues) 'Finds the location of the column
If Not BU Is Nothing
Then 'If the value "BU" is
found...then
myaddress = Mid(BU.Address, 2)
BUcol = Left(myaddress, InStr(myaddress, "$") -
1) 'sets the location to a variable BUcol
NewSheet.Range("B3") = BUcol
NewSheet.Range("B4") = "1"
End If
NewSheet.Range("B5") = "2"
End With


End Sub

The other worksheet has that text in row1 formatted as either general,
or as text (does it matter?)

So what is wrong with my code?

Thanks Joel.

-Pogster

 

[pogster]

I think i figured it out. The With function is not enough for the Set
function to use the sheet specified by the With function. I.E., if i
include the line:

Sheets("Premiums Commissions").Select

before the "Set BU = Rows(1)..." then it works like a charm. I guess
it only works on the ActiveSheet?